My couters are not recognized in IF STATEMENT

[andreroux]

Hi All

I have a macro with 3 counters: STRT_NM1, START_ROW, COUNTER. I run a For...Next loop to execute a sub macro called DoWork. At the end of the DoWork Sub Macro, I display my counters to see if they are incremented, and they are incremented as I expect. Yet, In an IF statement, I want to select a cell by using the statement Range("D" & STRT_NM1).Select where cells D2, D3, D4, etc must be selected. For some strange reason, it keeps selecting D2, as if the counter STRT_NM1 remains at value 2, but my display at the end of the routine shows different. Any Help? Is there another way I can select the cells other than using Range("D" & STRT_NM1).Select?

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
' ============================
' Define Variables & Populate them
' ============================
   Dim fname As String
   Dim STRT_NM1 As Long
   Dim START_ROW As Long
   Dim INVCNT As Long
   Dim COUNTER As Long
   Dim ws1 As Worksheet
   STRT_NM1 = 1
   START_ROW = 6
   COUNTER = 1
   
' ==================================================
' Copy First Invoice Detail to Invoice Control Sheet
' ==================================================
        Workbooks.Open Filename:= _
        "C:\Users\E-Machine\Dropbox\Technology Station - VUT\Master Invoice TS.xlsm"
        Sheets("Invoices").Select
        Range("D" & STRT_NM1).Select
        INVCNT = Range("C2").Value
    For COUNTER = 1 To INVCNT
        DoWork START_ROW, STRT_NM1, COUNTER
    Next COUNTER
End Sub
Sub DoWork(ByVal START_ROW As Long, ByVal STRT_NM1 As Long, COUNTER As Long)
    If START_ROW > 0 Then
        START_ROW = START_ROW + 1
        STRT_NM1 = STRT_NM1 + 1
    Else
        START_ROW = 7
        STRT_NM1 = 2
    End If
        Windows("Master Invoice TS.xlsm").Activate
        Sheets("INVOICES").Select
        Range("D" & STRT_NM1).Select
        Dim fname As String
        fname = "C:\Users\E-Machine\Dropbox\Technology Station - VUT\Invoices\" & ActiveCell.Value
        Workbooks.Open fname
        Cells.Select
        Selection.Copy
        ActiveWindow.Close
        Windows("Invoice Control Sheet.xlsm").Activate
'        Sheets.Add After:=ActiveSheet
        Sheets("Sheet2").Select
        ActiveSheet.Paste
        ActiveSheet.Range("I1:J1").Select
        Selection.Copy
        Sheets("Sheet1").Select
        Range("J" & START_ROW).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("I2:J2").Select
        Application.CutCopyMode = False
        Selection.Copy
'        Application.WindowState = xlMinimized
        Windows("Master Invoice TS.xlsm").Activate
        Application.WindowState = xlNormal
        Windows("Invoice Control Sheet.xlsm").Activate
        Sheets("Sheet1").Select
        Range("B" & START_ROW).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("I3:J3").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("C" & START_ROW).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("B5:D5").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("D" & START_ROW).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("I4:J4").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("G" & START_ROW).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
        Sheets("Sheet2").Select
        Range("I21:J21").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("K" & START_ROW).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False
        Range("K" & START_ROW).Select
        Application.CutCopyMode = False
        Selection.Style = "Comma"
        Sheets("Sheet2").Select
'        ActiveWindow.SelectedSheets.Delete
        Cells.Select
        Selection.ClearContents
        Windows("Invoice Control Sheet.xlsm").Activate
        Application.WindowState = xlNormal
        STRT_NM1 = STRT_NM1 + 1
       START_ROW = START_ROW + 1
        Range("E1").Value = START_ROW
        Range("E2").Value = STRT_NM1
        Range("E3").Value = COUNTER
        Sheets("Sheet1").Select
  
End Sub

 

[patel45]

with Range("D" & STRT_NM1).Select you select only one cell, not a range of cells, you have to use Range("D2:D" & STRT_NM1).Select

 

[AlphaFrog]

The Do_Work procedure only receives the variables values because of ByVal . If you use ByRef, that allows Do_Work to change the variables from the caller.

Sub DoWork(ByRef START_ROW As Long, ByRef STRT_NM1 As Long, ByVal COUNTER As Long)

Passing Variables By Reference And By Value

 

[andreroux]

Hi All

I just need to say a sincere THANK YOU for all the help I received from the people in this forum. It certainly pointed me in the right direction. I think you guys are AWESOME!

André Roux