Loop (Excel 2002)

[HUHandEH]

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

[HansV]

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.

[HUHandEH]

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

[Jezza]

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

[HansV]

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>

[HUHandEH]

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

[LegareColeman]

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.

[HUHandEH]

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

[HansV]

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.

[HUHandEH]

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.

[HansV]

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.