Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Find and Replace values from multiple lookup values in Excel
#1
Hi Guys,

Having a lot of trouble with what must be a simple macro of two parts:

Part 1:

I am trying to look up values in multiple worksheets from a list of lookup values in the last sheet in the workbook (titled "exclusives" in column B2:B250). Once it finds these exact values in all other worksheets (excluding the "exclusive" sheet) they all should be replaced by the value "Brand".
____________________

Part 2:

After this macro I then need a second macro which will look up all new "Brand" values in the whole work book and then replace the preceding eight cells (columns C - J) to the right of "Brand" valued cells with "" (i.e. clear contents).

If anyone has any ideas how I can get this to work I would be very very grateful. I am having great problems with this.

Many Thanks,

Greg
#2
Code:
Copy      Help
;PART 1

;get lookup values
ExcelSheet es.Init("exclusives")
ARRAY(str) a
es.GetCells(a "B2:B250")

int i
;for(i 0 a.len) out a[0 i]

;enum sheets
Excel.Worksheet ws
foreach ws es.ws.Application.Worksheets
,str name=ws.Name
,if(name~"exclusives") continue
,;search and replace
,Excel.Range ru ra f
,ru=ws.UsedRange
,ra=ws.Cells
,for i 0 a.len
,,;r.Replace(a[0 i] "Brand") ;;this would be easier and probably faster, but shows message box if not found
,,
,,f=ru.Find(a[0 i] @ @ @ @ 1)
,,rep
,,,if(!f) break
,,,f.Value="Brand"
,,,f=ru.FindNext(f)
,,
,;PART 2
,
,;find "Brand" cells
,int j col row col0 row0
,f=ru.Find("Brand" @ @ @ @ 1)
,for j 0 1000000000
,,if(!f) break
,,col=f.Column; row=f.Row
,,if(!j) col0=col; row0=row ;;save first cell position
,,else if(col=col0 and row=row0) break ;;stop searching because returned to the beginning
,,;out "%c%i" '@'+col row
,,
,,;clear next 8 cells in the row
,,str s.format("%c%i:%c%i" 'A'+col row 'A'+col+7 row)
,,ra.Range(s).Clear
,,
,,f=ru.FindNext(f)
,,
,,
,,


Forum Jump:


Users browsing this thread: 1 Guest(s)