Value of a variable not incrementing in If Then Else Statement

anshikam

Board Regular
Joined
Aug 20, 2016
Messages
79
Hello,

I am trying to run an "If Then Else " statement through a database of records.
I would like to check every single record of the database to match a value stored in a variable.
If value matches variable then update another filed and go on to the next record to check the same.
If value does not match variable it still needs to go to the next record to check the same.

Problem I am facing is my Variable for current record DDCurrRow does not increment by +1.

The value of variable DDCurrRow remains at 2.
Unable to figure out why it does not increment....Any help would be greatly appreciated.

VBA Code:
leftcurrrecord = HeaderRowLeft + 1
'MsgBox " Row 1 with record is " & leftcurrrecord

Do

    Workbooks("Left.xls").Activate
    Worksheets("Out").Activate

    Dim LeftEmpCode As String
    Dim LeftEmpName As String
    Dim LeftEmpDept As String

    LeftEmpCode = Cells(leftcurrrecord, ColNumLeftEmpID).Value
    LeftEmpName = Cells(leftcurrrecord, ColNumLeftEmpName).Value
    LeftEmpDept = Cells(leftcurrrecord, ColNumLeftDept).Value

    MsgBox "Code: " & LeftEmpCode & vbCrLf & "EmpName: " & LeftEmpName & vbCrLf & "EmpDept: " & LeftEmpDept

    Do
     Workbooks("DD DATABASE.xlsm").Activate
     Worksheets("DATABASE").Activate
  
     Dim DDTotRows As Long
     Dim DDCurrRow As Integer
     DDCurrRow = 2
     DDTotRows = Cells(Sheets("DATABASE").Rows.Count, 1).End(xlUp).Row
     MsgBox "Database Total Rows " & DDTotRows & vbCrLf & " Current Row " & DDCurrRow
  
          
     Dim ColNumDDEmpID As Long
     ColNumDDEmpID = WorksheetFunction.Match("*EMP*ID*", ActiveWorkbook.Sheets("DATABASE").Range("1:1"), 0)
     'MsgBox ColNumDDEmpID
  
     Dim ColNumDDRemarks As Long
     ColNumDDRemarks = WorksheetFunction.Match("*REMARKS*", ActiveWorkbook.Sheets("DATABASE").Range("1:1"), 0)
     'MsgBox ColNumDDRemarks
             
     If Cells(DDCurrRow, ColNumDDEmpID).Value = LeftEmpCode Then
        Dim DDRemarksValue As String
        DDRemarksValue = Cells(DDCurrRow, ColNumDDRemarks).Value
        Cells(DDCurrRow, ColNumDDRemarks).Value = "LeftEmployee" & Now() & DDRemarksValue
        DDCurrRow = DDCurrRow + 1
    Else
        DDCurrRow = DDCurrRow + 1
     End If
 
   Loop Until DDCurrRow > DDTotRows

leftcurrrecord = leftcurrrecord + 1
Loop Until leftcurrrecord > recordcount
 
Last edited:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,919
Office Version
  1. 365
Platform
  1. Windows
I haven't worked through your code, but try taking these two lines outside of the Do...Loop & put them before the Do. Otherwise, every time through the loop DDCurrRow is set back to 2

VBA Code:
    Dim DDCurrRow As Integer
    DDCurrRow = 2
 

anshikam

Board Regular
Joined
Aug 20, 2016
Messages
79
Y
I haven't worked through your code, but try taking these two lines outside of the Do...Loop & put them before the Do. Otherwise, every time through the loop DDCurrRow is set back to 2

VBA Code:
    Dim DDCurrRow As Integer
    DDCurrRow = 2
Yup Works Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,261
Members
410,905
Latest member
Extjel
Top