using a variable in a formula

[jordanburch]

Hey Guys,

I have looked at other threads but just cant seem to figure it out

Range("ao2:ao6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],'MAY FY20 IDARRS'!C[6],0)"

I have the above formula I am trying to replace the MAY FY20 IDARRS with

ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS"

I have what the variables are below.
data_wbk3 = Right(data_wbk2, 5)
'ThisWorkbook.Worksheets("Variables").Range("A2").Value = data_wbk3
'data_wbk1 = "FY" & Right(data_wbk3, 2)
ThisWorkbook.Worksheets("Variables").Range("A3").Value = data_wbk1
data_wbk7 = Left(data_wbk3, 3)
'ThisWorkbook.Worksheets("Variables").Range("A7").Value = data_wbk7

data_wbk2 = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")
'ThisWorkbook.Worksheets("Variables").Range("A1").Value = data_wbk2

any help is greatly appreciated!

Jordan

 

[RoryA]

It looks like you want:

Range("ao2:ao6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],'" & data_wbk7 & " " & data_wbk3 & " IDARRS'!C[6],0)"

 

[jasonb75]

Perhaps (omitting the Variables sheet)

Dim myDate As String
myDate = InputBox("Enter month I.E. 08-MAY20", Default:="08-MAY20")

Range("ao2:ao6000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=MATCH(RC[-1],'" & Mid(myDate, 4, 3) & " FY" & Right(myDate, 2) & " IDARRS'!C[6],0)"

 

[jordanburch]

It looks like you want:

Range("ao2:ao6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],'" & data_wbk7 & " " & data_wbk3 & " IDARRS'!C[6],0)"

so would it be like this instead?




Range("ao2:ao6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],'" & ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS'!C[6],0)"

is that how it should look? Im trying to avoid using the data_wbk variables because excel forgets them and I have them stored on the variables sheet.

Thanks Rory!

 

[jasonb75]

Im trying to avoid using the data_wbk variables because excel forgets them and I have them stored on the variables sheet.

Variables are only 'forgotten' when the code ends, with what you're attempting that shouldn't be a problem, however if it is, you could change Dim to Static in my suggestion (post 3).

 

[jordanburch]

thanks Jason and Rory. You both got me on the right track. This has worked

Range("ao2:ao6000").SpecialCells(xlCellTypeVisible).Formula = "=+MATCH(RC[-1],'" & ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS'!C[6],0)"


I appreciate both your responses and help

 

[RoryA]

FWIW, you should really be using FormulaR1C1 rather than Formula.

 

[jordanburch]

FWIW, you should really be using FormulaR1C1 rather than Formula.

Range("ao2:ao6000").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=+MATCH(RC[-1],'" & ThisWorkbook.Worksheets("Variables").Range("A7").Value & " " & ThisWorkbook.Worksheets("Variables").Range("A3").Value & " IDARRS'!C[6],0)"

so you mean like that? why is that?

 

[jasonb75]

It might work, but you shouldn't need the 'Variables' sheet, vba is capable of holding all of the necessary variables for as long as they are required. I thought the same as you when I first started with vba, I also found that bad habits lead to more bad habits

If you're not using the variables outside of the current 'macro' then all should work fine with standard declarations. If you need them to work with other procedures (macros) then you would need to declare them at either module, or workbook level (see link below)

VBA Variables - Lifetime and Scope

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

 

[jordanburch]

It might work, but you shouldn't need the 'Variables' sheet, vba is capable of holding all of the necessary variables for as long as they are required. I thought the same as you when I first started with vba, I also found that bad habits lead to more bad habits

If you're not using the variables outside of the current 'macro' then all should work fine with standard declarations. If you need them to work with other procedures (macros) then you would need to declare them at either module, or workbook level (see link below)

VBA Variables - Lifetime and Scope

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

thanks for your insight. I looked and looked and it seemed I could only do it inside a sheet. I need to to remember the variable even if I closed the workbook and this has seemed to work. so instead of DIM use Static and that would solve my issue?