Using sheet references in VBA on a VLOOKUP

[PittTrack]

I have been reading the forum for a while and have usually found what I needed from a search. This time that’s not the case… thus the post. I’m not the best coder so I use the macro recording to do some of my dirty work. If you have another idea besides how I did something in my example please post the whole macro or just correct it in the code and then attach the spreadsheet, thanks

I have three worksheets (orig [sheet1], hubs [sheet2], non hubs [sheet3]). The sheet named ORIG is the original data which gets pasted in every day so the rows are dynamic, the columns are static though. I have gotten one macro (OrigTabChanges) to do what I need on the ORIG tab. Once that is done I need to copy some of the data into Sheet2 (named HUBS) and other data into Sheet3 (named non hubs) from Sheet1

I thought about doing a INDEX/MATCH on it but I couldn’t get that to go so I used the VLOOKUP instead.

Below is the one formula used from the Macro Recorder.
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],orig!R[1]C[-1]:R[5523]C[10],2,FALSE)"
I want to make it so that I don’t have to do the active cell but rather use a range that is dynamic similar to below: (this don’t work)

Sheet2.Range("C3:C" & OriginalRowsCounted).FormulaR1C1 = _
"=VLOOKUP(RC[-1],Sheet1.R[1]C[-1]:M & HubRowsCounted]C[10],2,FALSE)"
So can it be done and how?


Edited: Removed attachement since it might have confused others.
(Data was modified from original data so it was useless to others)

Edited 10-Aug-07 by geekgirlau. Reason: insert line breaks

[austenr]

Hi, welcome to the board. We will need more information on your sample to help you. Please provide as much information as possible in order for us to help. Make sure you sanitize your data before posting.

[PittTrack]

Well I steped out for a bit and got an idea.... Here is a formula that works for all the data in column C, I just need to make more lines to fill the other columns (C-M)

Here is what I have... hopefully it helps someone


Sub HubTabChanges()

Dim rngLookupValue As Range
Dim rngtable As Range
Dim lngColIndex As Long
Dim blnRangeLookup As Boolean

OrigRowsCounted = Sheet1.UsedRange.Rows.Count
HubRowsCounted = Sheet2.UsedRange.Rows.Count
Set rngLookupValue = Sheet2.Range("B2:B" & HubRowsCounted)
Set rngtable = Sheet1.Range("B3:M" & OrigRowsCounted)
lngColIndex = 2
blnRangeLookup = False

Sheet2.Range("C2:C" & HubRowsCounted).Formula = _
Application.WorksheetFunction.VLookup(rngLookupValue, rngtable, lngColIndex, blnRangeLookup)

End Sub


Edited 10-Aug-07 by geekgirlau. Reason: insert line breaks

[Bob Phillips]

Sheet2.Range("C3:C" & OriginalRowsCounted).FormulaR1C1 = _
"=VLOOKUP(RC[-1],Sheet1!R[1]C[-1]:R[" & HubRowsCounted & "]C[10],2,FALSE)"


Edited 10-Aug-07 by geekgirlau. Reason: insert line breaks