M
Malcolm Hind
Using Excel 2002 (from Office XP)
I am trying to develope some VBA to process some data imported into an
excel sheet.
I need to loop throught all the Rows which I do with;
For i = MyRange.Rows.Count To 1 Step -1 (because I am deleting Rows,
reverse order)
Then at the same time I need to lookup some info based on a cell contents
in each of the rows. I do this by setting a new Range in each Row loop like
this;
Set MyLookUpRange = Range(Cells(Row.Row, 4), Cells(Row.Row, 8))
My intention is to use the contents of the cell in the 4th Column to lookup
another Range on another sheet and then copy in the data from that Range.
So my Cells(Row.Row,4) will contain an item code that I need to get into a
variable CGCode - then I need to process the MyLookUp Range doing a lookup
for each cell to map the data from the LookUp Range Columns into my sheet.
So it would look something like this (some pseudo code here);
CGCode = ?Get the Code to LookUp once for each Row Here
For Each iCell In MyLookUpRange.Cells 'Now loop the Row Range
If Cell NOT 1st cell in MyLookUpRange ' Because 1st cell has lookup Value
ColumnToGet = ?iCell Reference
'Do the LookUp - once fore each iCell but not the first
vLResult = CStr(Application.VLookup(CGCode, Range("Stock_Table"),
ColumnToGet, False))
'Paste the Value
iCell.Value = vLResult
Next
So I have a data sheet like this - LookUp code in column 4 (now) !;
?-?-?-1-a-xyz
?-?-?-2-b-dsf
?-?-?-3-c-fds
?-?-?-4-d-eds
A Stock_Table Range like this;
1-Black-32145
2-Red-94874
3-Blue-3849487
4-White-8398
and I would end up with a data sheet like this;
?-?-?-1-a-xyz -Black-32145
?-?-?-2-b-dsf -Red-94874
?-?-?-3-c-fds -Blue-3849487
?-?-?-4-d-eds -White-8398
the added lookup data shown on the right above for clarity - lots of
simplification here I have more complicated data but I presume that won't
matter as far as the method goes.
My main points are; Is the general method a reasonable one to pursue ?
When setting the CGCode (which I only need to do once for each Row) how do
I code that and then just fill in the iCells of MyLookUpRange that I am
filling in. I am a little bit stuck on those points though I want to say
that this is only a small part of a huge bit of processing that I have
solved with trail and (lots) of error ! So not asking everyone to do all my
work, but I am very grateful for the help I have received, thanks.
I am trying to develope some VBA to process some data imported into an
excel sheet.
I need to loop throught all the Rows which I do with;
For i = MyRange.Rows.Count To 1 Step -1 (because I am deleting Rows,
reverse order)
Then at the same time I need to lookup some info based on a cell contents
in each of the rows. I do this by setting a new Range in each Row loop like
this;
Set MyLookUpRange = Range(Cells(Row.Row, 4), Cells(Row.Row, 8))
My intention is to use the contents of the cell in the 4th Column to lookup
another Range on another sheet and then copy in the data from that Range.
So my Cells(Row.Row,4) will contain an item code that I need to get into a
variable CGCode - then I need to process the MyLookUp Range doing a lookup
for each cell to map the data from the LookUp Range Columns into my sheet.
So it would look something like this (some pseudo code here);
CGCode = ?Get the Code to LookUp once for each Row Here
For Each iCell In MyLookUpRange.Cells 'Now loop the Row Range
If Cell NOT 1st cell in MyLookUpRange ' Because 1st cell has lookup Value
ColumnToGet = ?iCell Reference
'Do the LookUp - once fore each iCell but not the first
vLResult = CStr(Application.VLookup(CGCode, Range("Stock_Table"),
ColumnToGet, False))
'Paste the Value
iCell.Value = vLResult
Next
So I have a data sheet like this - LookUp code in column 4 (now) !;
?-?-?-1-a-xyz
?-?-?-2-b-dsf
?-?-?-3-c-fds
?-?-?-4-d-eds
A Stock_Table Range like this;
1-Black-32145
2-Red-94874
3-Blue-3849487
4-White-8398
and I would end up with a data sheet like this;
?-?-?-1-a-xyz -Black-32145
?-?-?-2-b-dsf -Red-94874
?-?-?-3-c-fds -Blue-3849487
?-?-?-4-d-eds -White-8398
the added lookup data shown on the right above for clarity - lots of
simplification here I have more complicated data but I presume that won't
matter as far as the method goes.
My main points are; Is the general method a reasonable one to pursue ?
When setting the CGCode (which I only need to do once for each Row) how do
I code that and then just fill in the iCells of MyLookUpRange that I am
filling in. I am a little bit stuck on those points though I want to say
that this is only a small part of a huge bit of processing that I have
solved with trail and (lots) of error ! So not asking everyone to do all my
work, but I am very grateful for the help I have received, thanks.