Add if condition to my macro

[ansvk1]

Hi there,
I have a macro that (created with help from this form) worked fine for what i intend it to do, but later on i came to find that it needs to have a check to be performed on a cell value being empty or not before running the whole program. So, now when i tried to insert an IF condition its not taking it...being new to vba coding i do not understand what or why its saying that error message. Can someone help me write this piece of code, correctly?

thanks in advance!

Here is my code:
Sub Populating_Summary_Sheet()


Application.ScreenUpdating = False

Dim s1 As Excel.Worksheet
Dim s2 As Excel.Worksheet
Dim iLastCellS2 As Excel.Range
Dim iLastRowS1 As Long
Dim Cnt As Long
Dim LstSht As String

LstSht = InputBox("Please enter the last sheet number, like 103")
If LstSht = "" Then Exit Sub
Set s2 = Sheets("Summary")

For Cnt = 2 To CLng(LstSht)
Set s1 = Sheets(CStr(Cnt))

If Range("C28") = "" Then GoTo JUMP
Else ' -->do the below, and i unable to get this done right.

iLastRowS1 = s1.Cells(s1.Rows.Count, "C").End(xlUp).Row

Set iLastCellS2 = s2.Cells(s2.Rows.Count, "N").End(xlUp).Offset(1, 0)

s1.Range("B28:C" & iLastRowS1).Copy
iLastCellS2.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False

s1.Range("F28:H" & iLastRowS1).Copy
iLastCellS2.Offset(0, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False

JUMP:
Next Cnt

 

[Fluff]

Re: how to add if condition to my macro

Try this

Sub DelData()
    For Cnt = 2 To CLng(LstSht)
    Set s1 = Sheets(CStr(Cnt))
    
    If s1.Range("C28") <> "" Then
    
        iLastRowS1 = s1.Cells(s1.Rows.Count, "C").End(xlUp).Row
        
        Set iLastCellS2 = s2.Cells(s2.Rows.Count, "N").End(xlUp).Offset(1, 0)
        
        s1.Range("B28:C" & iLastRowS1).Copy
        iLastCellS2.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False
        
        s1.Range("F28:H" & iLastRowS1).Copy
        iLastCellS2.Offset(0, 5).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False
    End If
    
JUMP:
    Next Cnt

 

[Scott T]

Re: how to add if condition to my macro

You are using the one line form of IF so you do not need the else.

 

[JackDanIce]

Re: how to add if condition to my macro

Try:

Sub Popluate_Summary()
    
    Dim w       As Long
    Dim LRw     As Long
    Dim LRs     As Long
    Dim wksSum  As Worksheet
    
    Set wksSum = Sheets("Summary")
    
    Application.ScreenUpdating = False
    
    For w = 2 To Worksheets.count
        LRs = wksSum.Cells(Rows.count, 14).End(xlUp).row + 1
        With Sheets(w)
            If Len(.Cells(28, 3).Value) > 0 Then
                LRw = .Cells(.Rows.count, 3).End(xlUp).row - 27
                wksSum.Cells(LRs, 14).Resize(LRw, 2).Value = .Cells(28, 2).Resize(LRw, 2).Value
                wksSum.Cells(LRs, 19).Resize(LRw, 3).Value = .Cells(28, 6).Resize(LRw, 3).Value
            End With
        End With
    Next w
    
    Application.ScreenUpdating = False
    
    Set wksSum = Nothing
    
End Sub

 

[ansvk1]

Re: how to add if condition to my macro

thanks for the help guys! i was able to make it work using Fluff's suggestion and learnt something new from Scott's response. Also thank Jack for your response!

 

[Fluff]

Re: how to add if condition to my macro

Glad we could help & thanks for the feedback