Excel doesn't find things that are clearly there.

[Gantela]

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.

 

[RoryA]

Welcome to the forum.

You need to qualify the Cells calls with the same worksheet as the Range call:

Set columnofprice = Sheets("Data_Blue_" & mowin).Range(Sheets("Data_Blue_" & mowin).Cells(4, kcellfirstcolumn), Sheets("Data_Blue_" & mowin).Cells(4, kcelllastcolumn)).Find(price, LookIn:=xlFormulas, lookat:=xlWhole)

 

[Gantela]

Wow, this worked! I had no idea you are supposed to do this (if sheet is already declared beforehand...)! Thank you very much!