Hello, I am looking for some help creating a macro for find/replace.

What I am looking to do is look at a cell say A1, take the values in the
cell, and use it to replace "X" in a vlookup.

For example

A1 = 5

replace

=vlookup(c1,"Sheet X",A:D,2,false)

Thank you in advance.

Hi ryan,

Hope I have interpreted your question correctly.

Note:- The space and underscore at the end of a line is a line break in an
otherwise single line of code. You may know this but in case you don't,
telling you now saves confusing you.

Sub InsertFormula()

Dim createFormula As Variant

'Assumes cell A1 on Sheet1 contains the required sheet number

createFormula = "=VLOOKUP(C1,Sheet" & _
Sheets("Sheet1").Range("A1") & "!A1:D250,2,FALSE)"

'Insert the formula in cell B1 on sheet 1
Sheets("Sheet1").Range("B1") = createFormula

End Sub


Sub InsertFormula2()
'Alternative method without using variable

Sheets("Sheet1").Range("B1") = "=VLOOKUP(C1,Sheet" & _
Sheets("Sheet1").Range("A1") & "!A1:D250,2,FALSE)"

End Sub

--

Regards,

OssieMac


"Ryan" wrote:

Hello, I am looking for some help creating a macro for find/replace.

What I am looking to do is look at a cell say A1, take the values in the
cell, and use it to replace "X" in a vlookup.

For example

A1 = 5

replace

=vlookup(c1,"Sheet X",A:D,2,false)

Thank you in advance.

Hi again Ryan,

I just realized that you used the column references A:D only without the row
numbers for the Lookup Range. You can use that in lieu of the range that I
used.
--
Regards,

OssieMac


"OssieMac" wrote:

Hi ryan,

Hope I have interpreted your question correctly.

Note:- The space and underscore at the end of a line is a line break in an
otherwise single line of code. You may know this but in case you don't,
telling you now saves confusing you.

Sub InsertFormula()

Dim createFormula As Variant

'Assumes cell A1 on Sheet1 contains the required sheet number

createFormula = "=VLOOKUP(C1,Sheet" & _
Sheets("Sheet1").Range("A1") & "!A1:D250,2,FALSE)"

'Insert the formula in cell B1 on sheet 1
Sheets("Sheet1").Range("B1") = createFormula

End Sub


Sub InsertFormula2()
'Alternative method without using variable

Sheets("Sheet1").Range("B1") = "=VLOOKUP(C1,Sheet" & _
Sheets("Sheet1").Range("A1") & "!A1:D250,2,FALSE)"

End Sub

--

Regards,

OssieMac


"Ryan" wrote:

Hello, I am looking for some help creating a macro for find/replace.

What I am looking to do is look at a cell say A1, take the values in the
cell, and use it to replace "X" in a vlookup.

For example

A1 = 5

replace

=vlookup(c1,"Sheet X",A:D,2,false)

Thank you in advance.