Search code examples
vbaif-statementforeachskip

If not...Else skip


I found a question really close to mine but as the answer was specific to the question I couldn't quite relate it to mine.

I am using an if not function to populate a column that already has values in it and I want it to overwrite the values if the conditions exist and if they don't I want it to leave the value how it is.

In other words I want the "else" part of the function to say skip or something like that... I've tried "null" "skip" and a few others but no luck.

Below is a portion of my code in case it's necessary ignore problems in my code this isn't the finished product just the basic idea in case you needed some variables. Thank you in advance!:

Sub ReportCompareAlta() ' ' ReportCompareAlta Macro ' Adds instances where column D is "ALTA"

Dim varSheetA As Variant
Dim varSheetB As Variant
Dim varSheetC As Variant
Dim StrValue As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Dim WbkA As Workbook
Dim WbkB As Workbook
Dim WbkC As Workbook
Dim counter As Long

Set WbkA = Workbooks.Open(Filename:="G:\Reporting\AH_MISSE_FEB2013.xls")
Set WbkB = Workbooks.Open(Filename:="G:\Reporting\AH_MISSE_MAR2013.xls")
Set WbkC = Workbooks.Open(Filename:="G:\Reporting\ReportCompare.xls")
Set varSheetA = WbkA.Worksheets("LocalesMallContratos")
Set varSheetB = WbkB.Worksheets("LocalesMallContratos")
Set varSheetC = WbkC.Worksheets("Sheet1")


strRangeToCheck = "A1:IV65536"

Debug.Print Now
varSheetA = WbkC.Worksheets("Sheet2").Range(strRangeToCheck) 'may be confusing code here
varSheetB = WbkC.Worksheets("Sheet3").Range(strRangeToCheck) 'may be confusing code here
Debug.Print Now

counter = 0

For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)        
        If varSheetB(iRow, "B") = varSheetA(iRow, "B") & varSheetB(iRow, "B") <> "GERENCIA" & varSheetB(iRow, "B").Value <> "" & varSheetB(iRow, "D") = "ALTA" Then
            StrValue = ""
            varSheetB.Range("iRow:B").Select 
            Selection = StrValue
            ActiveSheet = varSheetC
            Range("A1").Select
            Selection.Offset(counter, 0).Value = StrValue
            counter = counter - 1

        Else
            "SKIP"
        End If         
Next iRow

End Sub


Solution

  • Simply remove the Else:

        If varSheetB(iRow, "B") = varSheetA(iRow, "B") & varSheetB(iRow, "B") <> "GERENCIA" & varSheetB(iRow, "B").Value <> "" & varSheetB(iRow, "D") = "ALTA" Then
            StrValue = ""
            varSheetB.Range("iRow:B").Select 
            Selection = StrValue
            ActiveSheet = varSheetC
            Range("A1").Select
            Selection.Offset(counter, 0).Value = StrValue
            counter = counter - 1
    
        End If