Excel VBA not recognizing cell value

I don't follow what you are asking. Can you elaborate?

I think the OP is saying

A1 contains a formula.
Using Range("A1").value results in...presumably the formula not the value ? (Insert your answer here OP)
However if the OP overwrites the value in A1 using copy and paste values the same Range("A1").value codes results in the expected value.

Am not sure why Range("A1").value would return anything other than the value that A1 contains.

Prob best to show your code or attach a spreadsheet.

Range("A1").Value should return the result of the formula and not the formula. If it doesn't, there is something else going on in your code that's not apparent from your description. Can you post your code?

Dates are funny things in Excel. They are numeric values that are formated to display as dates.

The quick answer is this...

strCol = Format(.Range("C14").Value, "MM-DD-YYYY")

Change the date format to the same date format as in row 1

Can you post an example of the formulas that are returning dates?

Originally Posted by her.rockstar

AlphaFrog I'm afraid that didn't work.

For the code were you suggesting this?:

With wsSource
    valChange = .Range("C16").Value
    strCol = Format(.Range("C14").Value, "MM-DD-YYYY")
    strRow = .Range("C18").Value
End With

That's what I was suggesting. Is MM-DD-YYYY the correct date format? I don't know what date format you are actually using.

What happens if you try Application.Match instead of Find?

For example instead of this,

With wsDest
    Set colCell = .Range("1:1").Find(strCol)
    Set rowCell = .Range("A:A").Find(strRow)
    
    'Error check
    If colCell Is Nothing Then
        MsgBox "Could not find Group name"
        Exit Sub
    ElseIf rowCell Is Nothing Then
        MsgBox "Could not find Date value"
        Exit Sub
    End If

    With .Cells(rowCell.Row, colCell.Column)
        If dosubtract Then
            .Value = .Value - valChange
        Else
            .Value = .Value + valChange
        End If
    End With
End With

something like this.

With wsDest
    Res = Application.Match(strCol, .Range("1:1"),0)
    Set rowCell = .Range("A:A").Find(strRow)
    
    'Error check
    If IsError(Res) Then
        MsgBox "Could not find Group name"
        Exit Sub
    ElseIf rowCell Is Nothing Then
        MsgBox "Could not find Date value"
        Exit Sub
    End If

    With .Cells(rowCell.Row, Res)
        If dosubtract Then
            .Value = .Value - valChange
        Else
            .Value = .Value + valChange
        End If
    End With
End With

Try adding this.

Dim Res As Variant

Try this...

Private Sub RecordHours_Click()
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim rowCell As Range
Dim colCell As Range
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim valChange As Double
Dim strCol As Date
Dim strRow As String
Dim dosubtract As Boolean

Set wsSource = Worksheets("Main")
Set wsDest = Worksheets("Hours")

'Gather data
With wsSource
    valChange = .Range("C16").Value
    strCol = Format(.Range("C14").Value, "mm/dd/yyyy")
    strRow = .Range("C18").Value
End With

With wsDest
    Set colCell = .Range("1:1").Find(strCol, , xlValues, xlWhole, , xlNext, False)
    Set rowCell = .Range("A:A").Find(strRow, , xlValues, xlWhole, , xlNext, False)
    
    'Error check
    If colCell Is Nothing Then
        MsgBox "Could not find Date"
    ElseIf rowCell Is Nothing Then
        MsgBox "Could not find Company"
    Else
        With .Cells(rowCell.Row, colCell.Column)
            If dosubtract Then
                .Value = .Value - valChange
            Else
                .Value = .Value + valChange
            End If
        End With
    End If

End With

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Originally Posted by her.rockstar

That worked! Like you widened the umbrella of what type of value it could search for right?

Thank you so much for doing this. I kind of borrowed this code from a different workbook I had - do you know if there's a simple chance I can make for a subtraction button?

You're welcome.

I don't know what you mean by a subtraction button. I have little understanding what you are attempting to do.

Also in the code, you don't set the variable dosubtract to anything so it is always False by default.

Sorry for the fogginess. I was attempting to build a test workbook for you, but you solved the problem too quickly!

Right now it's simply taking the number from C16 and adding it to the Hours worksheet in a specific cell. I'd like to have a button that would remove the amount in C16 from the specific cell, if possible. Not hugely necessary, though.

Originally Posted by her.rockstar

Sorry for the fogginess. I was attempting to build a test workbook for you, but you solved the problem too quickly!

Right now it's simply taking the number from C16 and adding it to the Hours worksheet in a specific cell. I'd like to have a button that would remove the amount in C16 from the specific cell, if possible. Not hugely necessary, though.

If you set the variable dosubtract = True that's exactly what the code will do.

Alternatively, make the value in C16 negative to subtract.