VBA code Adding a cell contains date and a cell contains a number, getting mismatch error

0
votes

Hi I am Trying to add to cells together and compare them against another cell but I get a type mismatch. first cell is a date, the one being added is a number"as in number of days" and the third one being compared is a date also. but I get type mismatch. my code is below

Sub Macro1()
Macro1 Macro

    Dim wks As Worksheet
    Set wks = ActiveSheet

    Dim x As Integer
    Dim p As Integer

    Dim rowRange As Range
    Dim colRange As Range

    Dim LastCol As Long
    Dim LastRow As Long
    LastRow = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row

    Set rowRange = wks.Range("A1:A" & LastRow)

    For i = 7 To 189
        p = 0
        For q = 8 To LastRow
            If [aq] = [si] Then
                If [cq] + [ui] >= [xi] Then
                    [oq] = 1
                Else
                    p = p + [dq]
                    [qq] = 0
                End If
             End If
         Next q     
     Next i   
End Sub

[cq] is a cell that contains date

[ui] is a cell that contains number

[xi] is a cell that contains date

2
vbaexceltype-mismatch
Where do you declare/define cq, ui, xi? – BruceWayne
@BruceWayne - my crystal ball tells me they are named ranges but I need to get it into the shop for a vagueness test. – user4039065
Are you sure that those cells contain dates and not a text string that looks like a date? – Scott Craner
Are you saying that [aq] = [si] is the equivalent of cells(q, "A") = cells(i, "S") ? – user4039065
yeah Im new to VBA and excel Macros by [cq] I mean Cell Cq and so on – Shahin Taghikhani

2 Answers

2
votes

Try it as cells(q, "A") = cells(i, "S").

    For i = 7 To 189
    p = 0
    For q = 8 To LastRow
         'If [aq] = [si] Then
         If cells(q, "A") = cells(i, "S") Then
            'If [cq] + [ui] >= [xi] Then
            If cells(q, "C") + cells(i, "U") >= cells(i, "X") Then
                '[oq] = 1
                cells(q, "O") = 1
            Else
                'p = p + [dq]
                p = p + cells(q, "D")
                '[qq] = 0
                cells(q, "Q") = 0
            End If
         End If
         Next q

      Next i
0
votes

You need to use the "DateAdd" function. Instructions here: https://www.techonthenet.com/excel/formulas/dateadd.php

Example:

Sub add_dates()


    Dim dateOne As Date
    Dim dateTwo As Date
    Dim lngDays As Long


    dateOne = "1/1/2018"
    lngDays = 2
    dateTwo = "1/3/2018"

    Dim result As Boolean
    If DateAdd("d", lngDays, dateOne) >= dateTwo Then
        MsgBox ("Greater than or equal to")
    Else
        MsgBox ("Less than")
    End If


End Sub