PittTrack
08-09-2007, 06:48 AM
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
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