Red over White
Board Regular
- Joined
- Jul 16, 2011
- Messages
- 123
- Office Version
- 365
- Platform
- 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
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