Code:
Sub experiemnt()
Dim G As Date
Dim W As Range
Dim rcell As Range
Dim ecell As Range
Dim kcell As Range
Dim mo As String
Dim mowin As String
Dim newrange As String
Dim dd As Date
Dim erange As Range
Dim WBNAME As String
Dim ddd As Date
Dim dwin As Date
WBNAME = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
D = Format(Sheets(11).Range("A4"), "dd-mm-yyy")
dd = Format((DateSerial(Year(Sheets(11).Range("A4")), Month(Sheets(11).Range("A4")) + 1, 0)), "dd-mm-yyyy")
mo = Format(Month(ActiveCell.Offset(0, -2).Value), "00")
mowin = Format(Month(ActiveCell.Offset(0, -3).Value), "00")
ddd = Format(ActiveCell.Offset(0, -2), "yyyy-mm-dd")
datewin = Format(ActiveCell.Offset(0, -3), "yyyy-mm-dd")
Set rcell = ActiveSheet.Cells.Find(what:=D, LookIn:=xlFormulas)
Set ecell = ActiveSheet.Cells.Find(what:=dd, LookIn:=xlFormulas)
Dim price As String
price = ActiveSheet.Range("O3")
Dim columnofprice As Range
Dim kcellr As String
Dim kcellc As String
Dim kcellmergecount As String
Dim kcellfirstcolumn As Integer
Dim kcelllastcolumn As Integer
If Not ActiveCell.EntireColumn.Find("Blue", LookIn:=xlValues, lookat:=xlPart) Is Nothing Then
Set kcell = Sheets("Data_Blue_" & mowin).Cells.Find(WBNAME, LookIn:=xlValues, lookat:=xlPart)
kcellr = kcell.Row
kcellc = kcell.Column
kcellmergecount = kcell.MergeArea.Columns.Count
kcellfirstcolumn = kcell.MergeArea.Column
kcelllastcolumn = kcellfirstcolumn + kcell.MergeArea.Columns.Count - 1
Set columnofprice = Sheets("Data_Blue_" & mowin).Range(Cells(4, kcellfirstcolumn), Cells(4, kcelllastcolumn)).Find(price, LookIn:=xlFormulas, lookat:=xlWhole)
Else
Set kcell = Sheets("Data_All_" & mo).Cells.Find(WBNAME, LookIn:=xlValues, lookat:=xlPart)
kcellr = kcell.Row
kcellc = kcell.Column
kcellmergecount = kcell.MergeArea.Columns.Count
kcellfirstcolumn = kcell.MergeArea.Column
kcelllastcolumn = kcellfirstcolumn + kcell.MergeArea.Columns.Count - 1
Set columnofprice = Sheets("Data_All_" & mo).Range(Cells(3, kcellfirstcolumn), Cells(3, kcelllastcolumn)).Find(price, LookIn:=xlFormulas, lookat:=xlWhole)
End If
End Sub
Please excuse me for the messy code, I am trying to make Excel find the cell address of a number based off other column data (thus narrowing down the search). Everything works up until the columnprice variable, where Excel throws errors at me...
Is it an issue of variable declaration or formatting? As soon as I try to use Range(Cells(3, kcellfirstcolumn), Cells(3, kcelllastcolumn)), instead of just Cells, it throws out an error.
The price variable is clearly there in theses sheets, written as a number 1.5, formatted as #0.00. In the original O3 cell its formatted as a number (maybe that is the issue?).
When doing a simple IF statement check, the cells match but excel can't find the cell and returns errors for columnofprice.