Dynamic Path based on Cell Value

Sowelu

New Member
Joined
Mar 18, 2009
Messages
27
Hi,

I have VBA code with array formula that looks up some values from series of spreadsheets. Which spreadsheet to use depends on the values in another column.

How do I make the formula the same for the whole range as it will substitute the necessary path into itself? :confused:


here is an example:

here is a code for VBA:

Code:
Range("I2").Select
    Selection.FormulaArray = _
        "=INDEX('1_Repl'!$C$2:$C$8,MATCH(1,IF('1_Repl'!$A$2:$A$8=H4,IF('1_Repl'!$C$2:$C$8<>0,1)),0))"
    Selection.AutoFill Destination:=Range("I2:I535"), Type:=xlFillDefault
TEST_LOOKUP.xls
ABCDEFGHIJK
11stSpreadsheet2ndSpreadsheet
2
3Number123CodeNumberData2
41230.200.211230.22Code=1,so1_Repl.xls
52340.10.1012340.1
63450002345#N/Ahastoreferto2_Repl.xls
712300.2203456#N/A
8234000.153567#N/Apathhastobedynamic
91230.240.240.243678#N/AValueofG2+"_Repl.xls"
103450.1300
11
1_Repl


Thanks a lot for help!!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
This might be just enough, using a string variable to hold the changing code number, and another string variable to hold the formula you create using the code:

Code:
Dim myCodeNum as String
Dim myFormula as String

myFormula = "=INDEX('" _
	& myCodeNum & "_Repl'!$C$2:$C$8,MATCH(1,IF('" _
	& myCodeNum & "_Repl'!$A$2:$A$8=H4,IF('" _
	& myCodeNum & "_Repl'!$C$2:$C$8<>0,1)),0))"

Range("I2").Select
    Selection.FormulaArray = myFormula
    Selection.AutoFill Destination:=Range("I2:I535"), Type:=xlFillDefault

Alex
 
Upvote 0

Sowelu

New Member
Joined
Mar 18, 2009
Messages
27
This might be just enough, using a string variable to hold the changing code number, and another string variable to hold the formula you create using the code:

Alex


Thank you Alex, I will try that. But something is missing: how do I assign values to myCodeNum?
 
Upvote 0

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Aack - sorry. Good point. How many rows do you have here? Formulaic version would be to use Indirect() and Address() to construct the sheet name right in the formula. Macro version could use code to enter the formula on each row - but wouldn't be good if there are many rows to process (more than 1000 or less if the formula is already an intensive one to calculate).
 
Upvote 0

Sowelu

New Member
Joined
Mar 18, 2009
Messages
27
ADVERTISEMENT
Aack - sorry. Good point. How many rows do you have here? Formulaic version would be to use Indirect() and Address() to construct the sheet name right in the formula. Macro version could use code to enter the formula on each row - but wouldn't be good if there are many rows to process (more than 1000 or less if the formula is already an intensive one to calculate).


I have between 4000 and 5000 rows...
But I dont mind it being slow -- I can wait few min for it to be calculated...
if there isn't any cleaner and faster way..
 
Upvote 0

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Okay, here's a shot. I might have preferred to use Indirect() in the formula so we don't need to use the "CodeNum" as in my code - maybe it was the wrong idea from the start. But I'm not with Excel today and I'm afraid I'd make a mistake building an Indirect formula without testing it. So here's the programmatic version.

1) tried to make it easy to maintain - so all the things you might need to set or change is at the top. Make sure you have the right numbers for the row where the formulas begin and what column to put them in. Also, the column we want to "check" to get the last row (how far down to fill). Also, the column that has the repl sheet numbers (codes).

2) Your picture has formulas starting in row 4 so I'm starting there too.

3) this is "dynamic" in the sense that its written to check column H to see how far down to fill - more or less a mimic of autofill behavior.

Hope it works...untested. Alex.

Code:
Sub foo()
Dim myCodeNum as String
Dim myFormula as String
Dim ws as Worksheet
Dim LRow as Long, x as Long, y as Long

Const ROW_WHERE_FORMULA_STARTS As Long = 4 '//First formula will be in this row
Const COLUMN_TO_PUT_FORMULA_IN As Long = 9 '//Column Number to write formulas in..."A" = 1, "B" = 2 etc.
Const COLUMN_TO_DETERMINE_LAST_ROW_BY As Long = 8 '//Excel will fill formulas down as far as last cell in this column
Const COLUMN_WITH_CODE_NUMBERS As Long = 7 '//Column with number to determine Repl sheet number

'--------------------------------------------

Set ws = ActiveSheet

With ws
	LRow = .Cells(Rows.Count,COLUMN_TO_DETERMINE_LAST_ROW_BY).End(xlUp).Row
	y = COLUMN_TO_PUT_FORMULA_IN

	For x = ROW_WHERE_FORMULA_STARTS to LRow
		
		'//Get sheet number to use in formula
		MyCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value

		'//Create a formula string
		myFormula = "=INDEX('" _
			& myCodeNum & "_Repl'!$C$2:$C$8,MATCH(1,IF('" _
			& myCodeNum & "_Repl'!$A$2:$A$8=H4,IF('" _
			& myCodeNum & "_Repl'!$C$2:$C$8<>0,1)),0))"

		'//write formula to cell
		.Cells(x,y).FormulaArray = myFormula
	Next x
End With

End Sub
 
Upvote 0

Sowelu

New Member
Joined
Mar 18, 2009
Messages
27
ADVERTISEMENT
Okay, here's a shot. I might have preferred to use Indirect() in the formula so we don't need to use the "CodeNum" as in my code - maybe it was the wrong idea from the start. But I'm not with Excel today and I'm afraid I'd make a mistake building an Indirect formula without testing it. So here's the programmatic version.

1) tried to make it easy to maintain - so all the things you might need to set or change is at the top. Make sure you have the right numbers for the row where the formulas begin and what column to put them in. Also, the column we want to "check" to get the last row (how far down to fill). Also, the column that has the repl sheet numbers (codes).

2) Your picture has formulas starting in row 4 so I'm starting there too.

3) this is "dynamic" in the sense that its written to check column H to see how far down to fill - more or less a mimic of autofill behavior.

Hope it works...untested. Alex.

Alex, it works!! -- Thanks so much!
Code runs about 5 min. but it's ok, the only problem I have is while it runs
Excel prompt the window to open the file

Update Values: _Repl.xls

about 30-40 times throughout the calculation. I just cancel or escape out of it and code continues to run fine and the result is all good.

Is there a way to avoid this window to pop-up?
 
Upvote 0

JasonC71

Board Regular
Joined
Feb 10, 2008
Messages
159
Try...

After the line:
Set Ws = ActiveSheet

Insert:
Application.DisplayAlerts = False

Then, after the line:
End With

Insert:
Application.DisplayAlerts = True
 
Upvote 0

Sowelu

New Member
Joined
Mar 18, 2009
Messages
27
Try...

After the line:
Set Ws = ActiveSheet

Insert:
Application.DisplayAlerts = False

Then, after the line:
End With

Insert:
Application.DisplayAlerts = True

Thank you! It runs well now!

I have another question...
After I run this code, I copy-paste to keep only values and replace #N/A's with blank cells.

I need then to go over the column, and if there is a blank cell, I need to run another formula for those. I didn't put it all in an if-statement in formula because of length, so I am doing it by steps.

Basically, the code should be similar, but how do I check for blank cells and run formula for those?
 
Upvote 0

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Okay, continuing on ...

1) Added Jason Edits (thanks Jason!)
2) We run the same loop a second time to hard copy values, and then a third time. In the third loop I'm not actually doing anything yet - what's the "formula" there...maybe you can edit it or post back.

Alex

Code:
Sub foo()
Dim myCodeNum As String
Dim myFormula As String
Dim ws As Worksheet
Dim LRow As Long, x As Long, y As Long

Const ROW_WHERE_FORMULA_STARTS As Long = 4 '//First formula will be in this row
Const COLUMN_TO_PUT_FORMULA_IN As Long = 9 '//Column Number to write formulas in..."A" = 1, "B" = 2 etc.
Const COLUMN_TO_DETERMINE_LAST_ROW_BY As Long = 8 '//Excel will fill formulas down as far as last cell in this column
Const COLUMN_WITH_CODE_NUMBERS As Long = 7 '//Column with number to determine Repl sheet number

'--------------------------------------------

Set ws = ActiveSheet
Application.DisplayAlerts = False

With ws
    '//FIRST PASS-----------------------------------------------------------
    LRow = .Cells(Rows.Count, COLUMN_TO_DETERMINE_LAST_ROW_BY).End(xlUp).Row
    y = COLUMN_TO_PUT_FORMULA_IN

    For x = ROW_WHERE_FORMULA_STARTS To LRow
        
        '//Get sheet number to use in formula
        myCodeNum = .Cells(x, COLUMN_WITH_CODE_NUMBERS).Value

        '//Create a formula string
        myFormula = "=INDEX('" _
            & myCodeNum & "_Repl'!$C$2:$C$8,MATCH(1,IF('" _
            & myCodeNum & "_Repl'!$A$2:$A$8=H4,IF('" _
            & myCodeNum & "_Repl'!$C$2:$C$8<>0,1)),0))"

        '//write formula to cell
        .Cells(x, y).FormulaArray = myFormula
    Next x

    '//SECOND PASS - hard copy values----------------------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        .Cells(x, y).Value = .Cells(x, y).Value
        If .Cells(x, y).Value = "#N/A" Then
            .Cells(x, y).ClearContents
        End If
    Next x
    
    '//THIRD PASS - do something with blank cells----------------------------
    For x = ROW_WHERE_FORMULA_STARTS To LRow
        If Len(.Cells(x, y).Value) = 0 Then
            '//Do Something with blank cell
        End If
    Next x

End With
Application.DisplayAlerts = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,195,853
Messages
6,011,969
Members
441,658
Latest member
Carlos O

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top