CODEWITHSUNDEEP
excelvba
Gokotai
Gokotai

Reputation: 153

Excel VBA For Loop - condition not being satisfied

I'm fairly new to VBA coding so please bear with me.

The aim of the subroutine below is to go through a range of cells which I have specified and copy and paste them elsewhere if they meet the following criteria:

  1. TankerCell (cell D3) is not empty
  2. TankerCell's value is numeric

If not, I want it to run the del macro, which will delete the row to which TankerCell belongs if it doesn't meet the above criteria.

I have declared the other subroutines CopyandPaste, del and IncrementDate on a seperate module.

CopyandPaste is simply a macro that copies and pastes a predefined selection to a predefined cell and then deletes the initial range (from which data was copied), IncrementDate simply adds one day to the previous date and del deletes a predefined selection from the table being copied.

So far, the subroutine copies and pastes all the items across to the new table I have set up...It doesn't delete any data that it should delete.

Can you spot why this is happening?

Sub BulkCandP()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim i As Integer
    Dim LoopMax As Range, TankerCell As Range
    Dim NumericCheck As Boolean, EmptyCheck As Boolean

    Set LoopMax = Sheets("Sheet1").Range("N3")
    Set TankerCell = Sheets("Sheet1").Range("D3")
    NumericCheck = IsNumeric(TankerCell.Value) 'check if cell data is a number
    EmptyCheck = IsEmpty(TankerCell.Value) 'check if cell data is present


    For i = 1 To LoopMax.Value 'for all rows for the date range
        If i = 1 Then
            Call CopyAndPaste
            Call IncrementDate 'for first entry, increment date on table by 1
        ElseIf i > 1 Then
            If (NumericCheck = False Or EmptyCheck = True) Then
                Call del
            Else
                Call CopyAndPaste 'bulk copy and paste values
            End If
        End If
    Next i

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Upvotes: 0

Views: 123

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Is this what you are trying?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Integer
Dim LoopMax As Range, TankerCell As Range
Dim NumericCheck As Boolean, EmptyCheck As Boolean

Set LoopMax = Sheets("Sheet1").Range("N3")
Set TankerCell = Sheets("Sheet1").Range("D3")

'~~> Run it at least once
Call CopyAndPaste
Call IncrementDate

For i = 1 To (LoopMax.Value - 1)
    NumericCheck = IsNumeric(TankerCell.Value) 'check if cell data is a number
    EmptyCheck = IsEmpty(TankerCell.Value) 'check if cell data is present

    If (NumericCheck = False Or EmptyCheck = True) Then
        Call del
    Else
        Call CopyAndPaste 'bulk copy and paste values
    End If
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Upvotes: 1

Related Questions