Select Cell using a Letter from another sheet as the column to choose

Hello,

I have a file where the cell I want to select changes e.g. sometimes its F or E or G etc but the row always stays the same. The column to select is always refereced in another sheet in the same cell. I was trying to use replace but I am failing miserably. I then want to have "1" entered select the cell to the right and have that as +1 and then auto fill to the end.

Basically I need to number the columns to use for Vlookups.

If someone could help it would be amazing..

Thanks


Sub numbering()
Sheets("Allocation").Select
Replace (Range("F3"), "F", Sheets("Index").Range("W59").Select)
ActiveCell.FormulaR1C1 = "=1"
ActiveCell.Offset(, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
ActiveCell.Select
Selection.AutoFill Destination:=Range("ActiveCell:CU3"), Type:=xlFillDefault
End Sub

Suggest you post a sample workbook with some typical data.

I have a file where the cell I want to select changes

Which sheet and which row?

The column to select is always referenced in another sheet in the same cell

Sheet name and cell address?

I was trying to use replace

Replace what in which sheet in what range?

I then want to have "1" entered

In which cell and in which sheet?

auto fill to the end

It looks like to the right in your code. What determines the end?

BTW, Selecting should be sparingly used if at all.
Explain what you want to do and I am sure someone will come up with an understandable code (without selecting)

If Sheets("Sheet2") Cell C5 has the letter "K" (without quotations marks) in it

Please Login or Register  to view this content.

Will select Cell K6 in your active sheet.

Like InvisibleMan suggested, attach your workbook, without personal data, with before and after.

We have to start somewhere.
Let us know if this is part of the solution.
If it is, let us know what you want next.

This assumes that the Column letter is in Cell A1 in a sheet named "Index"
Change as required.

Please Login or Register  to view this content.

Oh my god I am very excited! yes that is what I am doing! Now is says 1 all the way across but I need it to say 1 then next one is 2 and next 3 etc.

I don't know how you figured out my horrible explanation. I have a presentation in 2 hours so really appreciate this!

See if this works.

Please Login or Register  to view this content.

Or, if you want to

Please Login or Register  to view this content.