VBA Indirect formula with each element from different places

Red over White

Board Regular
Joined
Jul 16, 2011
Messages
96
Office Version
  1. 2019
Platform
  1. MacOS
Having successfully used a MrExcel response to design the bulk of this macro, I have fallen on the final line - which wasn't within the response.

I'm struggling with the quotation marks 'furniture' within the INDIRECT formula, which combines three different elements - sheet, column and row.

I have included the whole macro in case there is something further up which is relevant. (The MrExcel response had an element if what was being looked for wasn't found. I have removed this element as I know it will there, I just need to know where.)

Any suggestions?

Thanks

Sub Stock()

Application.ScreenUpdating = False

Sheets("Stock10").Select

Dim b As Range, FoundCols As Range
Dim FirstA As String

With Sheets("Stock10")

Range(Cells(2, 8), Cells(2, 50)).Select
Set b = .Cells.Find(What:="LBT", After:=.Cells(Rows.Count, 1), LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

If Not b Is Nothing Then

FirstA = b.Address
If FoundCols Is Nothing Then
Set FoundCols = b
Else
Set FoundCols = Union(b, FoundCols)
End If
FoundCols.Select
End If

End With

Sheets("Stock12").Select

Dim c As Range, FoundCells As Range
Dim FirstB As String

With Sheets("Stock12")

Range(Cells(1, 3), Cells(1, 50)).Select
Set c = .Cells.Find(What:="LBT", After:=Selection(Rows.Count, 1), LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

If Not c Is Nothing Then

FirstB = c.Address
If FoundCells Is Nothing Then
Set FoundCells = c
Else
Set FoundCells = Union(c, FoundCells)
End If
FoundCells.Select
End If

End With

' FoundCols finds column AB on Stock10 worksheet
' FoundCells finds the correct column on Stock12
' Stock10!AB11 is the formula I'm trying to replicate in the INDIRECT element below


Sheets("Stock12").Cells(66, FoundCells).Formula = "=INDIRECT(Stock10! & FoundCols & 11)"

End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,534
Office Version
  1. 365
Platform
  1. Windows
Does it need to be INDIRECT, or just the correct reference? You don't need to use INDIRECT to construct a formula range in vba unless it still needs to be included in the formula for other reasons, for example if the sheet name is in a cell and the formula needs to reflect that changing.

From what I can see, this should do what you need, although I haven't analysed your full code, only the line that you want changed. When you post code without tags, the formatting is lost and it blends in with the rest of the post too much making it harder to read.
VBA Code:
Sheets("Stock12").Cells(66, FoundCells).Formula = "=Stock10!" & FoundCols.Address & ")"
 

Red over White

Board Regular
Joined
Jul 16, 2011
Messages
96
Office Version
  1. 2019
Platform
  1. MacOS
Thanks for your help.

Other macros will dictate which column will be deleted, which could be on either side of what this macros is looking at. I can't use the correct reference as the column element could be variable, and was my reason for using INDIRECT.

Your reply has highlighted an error I hadn't picked up on, in that the first half of the line should read (in blue):

Sheets("Stock12").Cells(66, FoundCells.Address).Formula = "=Stock10!" & FoundCols.Address & ")"

However, I didn't understand your second half as there is no reference to '11' and no opening bracket to match the end. I have tried various combinations, including "=(Stock10!" & FoundCols.Address & 11 & ")"" - to no avail.
 

Red over White

Board Regular
Joined
Jul 16, 2011
Messages
96
Office Version
  1. 2019
Platform
  1. MacOS
I found a slightly untidy solution in that row 66 is the last row. I extend the formula downwards and then used

Sheets("Stock12").Cells(66, ActiveCell.Column).Formula = Sheets("Stock12").Cells(67, ActiveCell.Column).Formula
 
Solution

Forum statistics

Threads
1,141,204
Messages
5,704,954
Members
421,372
Latest member
Jamie11

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