VBA Indirect formula with each element from different places

[Red over White]

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

 

[jasonb75]

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.

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

 

[Red over White]

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]

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