Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Yet More Excel VBA Help Please

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 07:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"