Use a variable inside an equation that is entered with VBA

[kengooch]

I have a loop that reads through worksheets in a workbook and creates equations in specific cells to parse data. The problem is that one of my equations does a lookup in a named range in another sheet and I need to be able to place a variable in the equation so that it will paste the correct value in the appropriate cell.

Here is the code I have created. You will see comments out beside several of the lines that explain what things are and then further down that reflect the issue I am having.

Here is the line in question:
Range("G13") = "=VLOOKUP(Surg!D13,luAcc,4)"
I need to replace the hard coded sheet name "Surg!" with the current variable assignment from the for next loop (vWS is the variable).

Here is the full code for this section. Would so appreciate any help on this.

Many thanks in advance!

= = = = = = = = = = = = = = = = = =
Sub mTitleEqu()
'Set Titles and Equations and Clear extraneous ending data
'Loop through Sheets
For Each vItem In Array("Surg", "SurgSup", "Cyto", "CytoSup") 'name of each sheet
vWS = vItem
'Find Total Cases to set vCopyEq
Sheets(vWS).Activate
Set vFind = ActiveSheet.Columns(1).Cells.Find(what:=" RR Verify", LookAt:=xlPart)
Range(vFind.Address).Select
vCopyEq = ActiveCell.Offset(-3, 0).Row
vEnd = ActiveSheet.UsedRange.Rows.Count
'Insert Column Titles and Equations
Range("A12").Select
Range("B12") = "Date" 'Column title
Range("B13") = "=IF(A13<>"""",LEFT(A13,12),"""")" ' equation to paste
Range("C12") = "Type/Yr"
Range("C13") = "=IF(A13="""","""",MID(A13,20,6))"
Range("D12") = "Acc #"
Range("D13") = "=IF(A13="""","""",(YEAR(B13)+(ABS(MID(A13,27,4)*0.0001))))"
Range("E12") = "Patient"
Range("E13") = "=IF(A13="""","""",TRIM(MID(A13,33,(LEN(A13)-45))))"
Range("F12") = "SSN"
Range("F13") = "=IF(A13="""","""",RIGHT(A13,11))"
Range("G12") = "Pathologist"
Range("G13") = "=VLOOKUP(Surg!D13,luAcc,4)" ' i need the sheet name from the variable vWS to be here instead of the hard coded sheet name.
'Copy Equations to end of data and clear extranious lines.
Range("B13:F13").Copy Range("B14:B" & vCopyEq)
Rows(vCopyEq + 1 & ":" & vEnd).Delete
Next
'Remove Dummy Records
'WorkBook Loop
For Each vItem In Array("Surg", "SurgSup", "Cyto", "CytoSup")
vWS = vItem
Sheets(vWS).Activate
'Worksheet Loop
With ActiveSheet
.AutoFilterMode = False
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.AutoFilter 1, "*ZZZ*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next
MsgBox "Titles and Calculations Finished." & vbCrLf & "Demo Records Removed."
End Sub

= = = = = = = = = = = = = = = = = = = = = = = =

 

[BobUmlas]

Change
"=VLOOKUP(Surg!D13,luAcc,4)"
to
"=VLOOKUP(" & vWS & "!D13,luAcc,4)"

 

[Rick Rothstein]

= = = = = = = = = = = = = = = = = =
Sub mTitleEqu()
'Set Titles and Equations and Clear extraneous ending data
'Loop through Sheets
For Each vItem In Array("Surg", "SurgSup", "Cyto", "CytoSup") 'name of each sheet
vWS = vItem
.....
..... < snip >
.....
Range("G13") = "=VLOOKUP(Surg!D13,luAcc,4)" ' i need the sheet name from the variable vWS to be here instead of the hard coded sheet name.
.....
..... < snip >
.....
End Sub

= = = = = = = = = = = = = = = = = = = = = = = =

Try that line of code like this...

Range("G13") = "=VLOOKUP(" & vWS & "!D13,luAcc,4)"

 

[kengooch]

Hey Rick! Thanks so much! It worked perfectly!!