Macro to add Multiple columns

J

jlclyde

My code is below. It keeps throwing an error on the fist for each.
It says Next without for. Any help will be greatly appreciated.

Thanks,
Jay

Sub SumHKCdays()
Dim DataSht As Worksheet
Dim Start As Double
Dim LstRow As Long
Dim RngHD As Range
Dim RngKD As Range
Dim RngCD As Range
Dim Rng As Range
Dim i
Dim Dt As Double
Dim X As Double
Dim Target As Range
Set Target = Range("H3")
Set DataSht = Sheet22
LstRow = DataSht.Range("A1").SpecialCells(xlCellTypeLastCell).Row
Set RngHD = DataSht.Range("A5:A" & LstRow)
Set RngKD = DataSht.Range("BC5:BC" & LstRow)
Set RngCD = DataSht.Range("CD5:CD" & LstRow)


Do Until Target.Roww = 32
If Target.Offset(0, -7) = "" Then
i = 0
Else
Start = Target.Offset(0, -7) - 13
Dt = Target.Offset(0, -7)

For Each i In RngHD
If i <= Dt And i >= Start Then
X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11)
+ _
i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 26) + _
i.Offset(0, 31) + i.Offset(0, 36) + i.Offset(0, 43) +
i.Offset(0, 48)
Else
X = X + 0
End
Next i

For Each i In RngKD
If i <= Dt And i >= Start Then
X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11)
+ _
i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 24)
Else
X = X + 0
End
Next i
For Each i In RngCD
If i <= Dt And i >= Start Then
X = X + i.Offset(0, 1) + i.Offset(0, 4) + i.Offset(0, 7) +
_
i.Offset(0, 12)
Else
X = X + 0
End
Next i
End If
i = X
Loop

End Sub
 
J

jlclyde

My code is below.  It keeps throwing an error on the fist for each.
It says Next without for.  Any help will be greatly appreciated.

Thanks,
Jay

Sub SumHKCdays()
    Dim DataSht As Worksheet
    Dim Start As Double
    Dim LstRow As Long
    Dim RngHD As Range
    Dim RngKD As Range
    Dim RngCD As Range
    Dim Rng As Range
    Dim i
    Dim Dt As Double
    Dim X As Double
    Dim Target As Range
    Set Target = Range("H3")
    Set DataSht = Sheet22
    LstRow = DataSht.Range("A1").SpecialCells(xlCellTypeLastCell).Row
    Set RngHD = DataSht.Range("A5:A" & LstRow)
    Set RngKD = DataSht.Range("BC5:BC" & LstRow)
    Set RngCD = DataSht.Range("CD5:CD" & LstRow)

   Do Until Target.Roww = 32
        If Target.Offset(0, -7) = "" Then
            i = 0
        Else
        Start = Target.Offset(0, -7) - 13
        Dt = Target.Offset(0, -7)

    For Each i In RngHD
        If i <= Dt And i >= Start Then
            X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11)
+ _
            i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 26) + _
            i.Offset(0, 31) + i.Offset(0, 36) + i.Offset(0, 43) +
i.Offset(0, 48)
        Else
            X = X + 0
        End
    Next i

    For Each i In RngKD
        If i <= Dt And i >= Start Then
            X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11)
+ _
            i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 24)
        Else
            X = X + 0
        End
    Next i
        For Each i In RngCD
        If i <= Dt And i >= Start Then
            X = X + i.Offset(0, 1) + i.Offset(0, 4) + i.Offset(0, 7) +
_
            i.Offset(0, 12)
        Else
            X = X + 0
        End
    Next i
    End If
    i = X
    Loop

End Sub
Click to expand...

I thought I had looked this over but I guess I had not. Please do not
repond to this post. End ifs are not in spelling is poor all over the
place. I will post back after I clean it up and if I am still having
problems.

Thanks,
Jay
 
J

John Bundy

Change your End to End If on all of them. Another error is:
Do Until Target.Roww = 32
Change to Roww.
 
R

Roger Govier

Hi

You are missing a lot of End If statements.
In one case it is omitted altogether, in other case you just have End.
Setting DataSht is incorrect

The following compiles OK

Sub SumHKCdays()
Dim DataSht As Worksheet
Dim Start As Double
Dim LstRow As Long
Dim RngHD As Range
Dim RngKD As Range
Dim RngCD As Range
Dim Rng As Range
Dim i
Dim Dt As Double
Dim X As Double
Dim Target As Range
Set Target = Range("H3")
Set DataSht = Sheets("Sheet22")
LstRow = DataSht.Range("A1"). _
SpecialCells(xlCellTypeLastCell).Row
Set RngHD = DataSht.Range("A5:A" & LstRow)
Set RngKD = DataSht.Range("BC5:BC" & LstRow)
Set RngCD = DataSht.Range("CD5:CD" & LstRow)


Do Until Target.Roww = 32
If Target.Offset(0, -7) = "" Then
i = 0
Else
Start = Target.Offset(0, -7) - 13
Dt = Target.Offset(0, -7)
End If
For Each i In RngHD
If i <= Dt And i >= Start Then
X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11) _
+ i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 26) _
+ i.Offset(0, 31) + i.Offset(0, 36) + i.Offset(0, 43) _
+ i.Offset(0, 48)
Else
X = X + 0
End If
Next i

For Each i In RngKD
If i <= Dt And i >= Start Then
X = X + i.Offset(0, 1) + i.Offset(0, 6) + i.Offset(0, 11) _
+ i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 24)
Else
X = X + 0
End If
Next i
For Each i In RngCD
If i <= Dt And i >= Start Then
X = X + i.Offset(0, 1) + i.Offset(0, 4) + i.Offset(0, 7) _
+ i.Offset(0, 12)
Else
X = X + 0
End If
Next i

i = X
Loop

End Sub
 
J

jlclyde

Hi

You are missing a lot of End If statements.
In one case it is omitted altogether, in other case you just have End.
Setting DataSht is incorrect

The following compiles OK

Sub SumHKCdays()
    Dim DataSht As Worksheet
    Dim Start As Double
    Dim LstRow As Long
    Dim RngHD As Range
    Dim RngKD As Range
    Dim RngCD As Range
    Dim Rng As Range
    Dim i
    Dim Dt As Double
    Dim X As Double
    Dim Target As Range
    Set Target = Range("H3")
    Set DataSht = Sheets("Sheet22")
    LstRow = DataSht.Range("A1"). _
    SpecialCells(xlCellTypeLastCell).Row
    Set RngHD = DataSht.Range("A5:A" & LstRow)
    Set RngKD = DataSht.Range("BC5:BC" & LstRow)
    Set RngCD = DataSht.Range("CD5:CD" & LstRow)

    Do Until Target.Roww = 32
        If Target.Offset(0, -7) = "" Then
            i = 0
        Else
            Start = Target.Offset(0, -7) - 13
            Dt = Target.Offset(0, -7)
        End If
        For Each i In RngHD
            If i <= Dt And i >= Start Then
                X = X + i.Offset(0, 1) + i.Offset(0, 6)+ i.Offset(0, 11) _
                    + i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 26) _
                    + i.Offset(0, 31) + i.Offset(0, 36) + i.Offset(0, 43) _
                    + i.Offset(0, 48)
            Else
                X = X + 0
            End If
        Next i

        For Each i In RngKD
            If i <= Dt And i >= Start Then
                X = X + i.Offset(0, 1) + i.Offset(0, 6)+ i.Offset(0, 11) _
                    + i.Offset(0, 16) + i.Offset(0, 21) + i.Offset(0, 24)
            Else
                X = X + 0
            End If
        Next i
        For Each i In RngCD
            If i <= Dt And i >= Start Then
                X = X + i.Offset(0, 1) + i.Offset(0, 4)+ i.Offset(0, 7) _
                    + i.Offset(0, 12)
            Else
                X = X + 0
            End If
        Next i

        i = X
    Loop

End Sub

--
Regards
Roger Govier










- Show quoted text -
Click to expand...

Setting the sheet works fine. The sheets name is not 22 it is the
code name that never changes. I really screwed up the End ifs though.

Thanks for the help,
Jay
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

On error from Find, GoTo E 1
Object required??? What object? 8
VBA calculate avg dates 1
Find string from column A in Range("B2:F7") list the header of that column/s 5
What is wrong with my UDF 4
sort and delete rows of information 4
Code works but goes into endless loop and crashes 1
Debug VBA code 6

Top