Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Loop (Excel 2002)

    Hi all,

    I'm trying to loop thru a range of cells and subtract 48 from each cell total in range set by the counter.

    I keep hitting a App defined error..can some one see what is wrong with my code...OR is there another way to subtract from a set range?

    Thanks

    Public Function LastRow()
    Dim lRow As Long
    If WorksheetFunction.CountA(Cells) > 0 Then
    lRow = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    LastRow = lRow
    End If
    End Function
    Public Sub do_the_volts()
    Dim I As Integer, Xrange As Range, sheetname As String, n As Integer
    sheetname = ActiveSheet.Name
    I = LastRow
    Debug.Print I
    With Worksheets(sheetname)
    Set Xrange = .Range("F2")
    End With
    Do Until I = 0
    n = n - 1
    With Xrange
    .Cells(n, 0) = .Value - 48#
    End With
    Loop
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop (Excel 2002)

    Your code doesn't make sense. You loop until I = 0, but you never change the value of I, so the loop has no end.

    Which range do you want to manipulate? You refer to cell F2, but by using .Cells(n, 0), you change cells in column E.

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Loop (Excel 2002)

    Hi Hans,
    I don't know where my brain is some times....
    This works for me, I understand my mistake thanks Hans....
    I would like to ask, is there a way to subtract the same amount from each cell within a column without doing a lengthy loop?

    Thanks.


    Public Sub dotherange()
    Dim I As Integer, Xrange As Range, sheetname As String, ilr As Integer
    sheetname = ActiveSheet.Name
    ilr = LastRow
    With Worksheets(sheetname)
    Set Xrange = .Cells(2, 7)
    End With
    Do Until I = ilr
    I = I + 1
    With Xrange
    .Cells(I, 0) = .Cells(I, 0).Value - 48#
    End With
    Loop
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Loop (Excel 2002)

    The original code was removed as it was flawed but I have updated this since my original post. This one works nicely

    Sub dotherange()

    Dim Bottom As Integer
    Dim Top As Integer
    Dim MyRange As Range

    Top = Range("F1").End(xlDown).Row 'tells you top of range
    Set MyRange = Range("F1").End(xlDown)
    Bottom = Range("F65532").End(xlUp).Row 'tells you the bottom of the range

    Set MyRange = MyRange.Resize(Bottom - Top + 1, 1)

    Range("A1").Select 'change the range value if this conflicts with another value
    ActiveCell.FormulaR1C1 = "48"
    Selection.Copy

    MyRange.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    End Sub
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop (Excel 2002)

    The way you'd do it interactively is to enter 48 in an empty cell, copy this cell to the clipboard, then select the range you want to modify, select Edit | Paste Special..., click Subtract then OK, and finally clear the cell in which you entered 48. In code, this would become something like
    <code>
    Sub DoTheRange()
    Dim ilr As Long
    ilr = LastRow
    With Range("F" & (ilr + 1))
    .Value = 48
    .Copy
    Range("F2:F" & ilr).PasteSpecial Operation:=xlSubtract
    .ClearContents
    End With
    End Sub</code>

  6. #6
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Loop (Excel 2002)

    Hey Jezza and Hans,

    Thanks for you Help, I figured it out with variation of your inputs, your help was much appreicated.
    Just want to post what I did.
    Public Sub Scoop()
    Dim llr As Long
    llr = LastRow1
    Range("F2:F" & llr).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
    SkipBlanks:=True, Transpose:=False
    Range("L1").ClearContents
    End Sub

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop (Excel 2002)

    I hope that there is more code than what you posted. The code you posted does not set llr, so it will be zero which will make the statement "Range("F2:F" & llr).Select" invalid since row numbers start with one not zero. Your code also does not copy anything to subtract which could also make the PasteSpecial statement invalid, depending on what is on the clipboard.
    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Loop (Excel 2002)

    Thanks Legare...

    I learned a a lot on this one, I couldn't figure out a couple of things, like how to give value to a variable and have it placed on the clipboard(Copy value).

    This does work for me, but I know you guys "Range("F2:F" & llr).Select" <----I didn't know you could do that when selecting a range object, is it right?

    Thanks.
    Darryl.



    Public Function LastRow1()
    Dim lRow As Long
    If WorksheetFunction.CountA(Cells) > 0 Then
    lRow = Cells.Find(What:="*", After:=[A1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    LastRow1 = lRow
    End If
    End Function

    Public Sub Scoop_the_Litter()
    Dim llr As Long
    llr = LastRow1
    Range("F2:F" & llr).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
    SkipBlanks:=True, Transpose:=False
    Range("L1").ClearContents
    End Sub

    Public Sub Title()

    Range("L1").Value = "48"
    Range("L1").Copy

    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop (Excel 2002)

    I don't understand your question, but in general, it's more efficient NOT to select cells in VBA code unless it's really necessary. The lines

    Range("F2:F" & llr).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
    SkipBlanks:=True, Transpose:=False

    can be merged into

    Range("F2:F" & llr).PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
    SkipBlanks:=True, Transpose:=False

    Why do you have a separate macro Title? It means you'll have to run two macros to get the job done instead of one.

  10. #10
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Loop (Excel 2002)

    I really didn't have a question, usually I post some thing and you MVP type people impress the heck out of me. I agree with the not making a selection, with your code that you posted earlier I couldn't figure out how to copy a value as a variable, like "48". without putting it on the sheet then copying it to the Clipboard. Is there a way to put information on the clipboard without putting it on the sheet? I googled it couldn't find anything.....I'm guessing the answer is no.

    The two seperate macros, I just cropped down the macro, there are about 14 different titles that I just group into one procedure so that I can organize them. It is from a Parsed Array, Text to columns....You helped me earlier with it.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop (Excel 2002)

    You could use the PutInClipboard method of the DataObject object to place information on the clipboard, but it's a bit technical. Do a search for DataObject or PutInClipboard if you're interested. Temporarily filling a cell is much easier.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •