PDA

View Full Version : [SOLVED] Multiplying cells in a range only if they have content



Vwhee
05-23-2012, 07:50 AM
Hi,

I've cobbled together some code that I'm hoping will multiply the cells in a range but only if that cell has content in it. It's part of a larger macro on a very large range of data but I've put together a little example of the data type and the part of the macro that doesn't seem to be working correctly. It's probably something really simple but I can't see where. :help

The Code I'm using is


Sub FindNumbers()
Sheets("Dates sheet").Select
Dim LRng As Range, URng As Range, F1Rng As Range
Dim cl As Variant
Set LRng = Sheets(1).Range("B3")
Set URng = Sheets(1).Range("I31").End(xlUp)
Set F1Rng = Range(LRng, URng)
For Each cl In F1Rng
If cl.Value > 1 Then
cl.Value = cl.Value * 2
End If
Next cl
End Sub

For some reason it works on some of the cells but not others and I'm not sure why.

Attached is the example spreadsheet.

Any help would be greatly appreciated - thank you

Vwhee

CatDaddy
05-23-2012, 09:17 AM
Sub FindNumbers()
'trying to multiply only cells with content by two
Sheets("Dates sheet").Select
Dim cl As Variant
For Each cl In Range("B3:I" & Range("I31").End(xlUp).Row)
If cl.Value > 1 Then
cl.Value = cl.Value * 2
End If
Next cl
MsgBox "Complete"
End Sub

Bob Phillips
05-23-2012, 09:34 AM
Public Sub FindNumbers()
Dim rng As Range
With ActiveSheet
Set rng = .UsedRange
With .Range("M1")
.Value = 2
.Copy
End With
Set rng = rng.SpecialCells(xlCellTypeConstants, 23)
rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
Range("M1").Value = ""
End With
Application.CutCopyMode = False
End Sub

Vwhee
05-24-2012, 01:14 AM
Hi
CatDaddy - I've tried your code and it does exactly the same as mine multiplies only 1 row (in fact the same row as mine - the first line of data it comes to) and seems to ignore everything below.
XLD - I've tried your code and it works perfectly on the example sheet I posted, however I will be using this code in a spreadsheet with about 40 columns of data and I only want to use this function on 8 columns of it and only certain rows within those columns. How do I change the range to be more specific in your code?

Thanks

Vwhee

Bob Phillips
05-24-2012, 01:24 AM
Are the 8 columns contiguous or dis-contiguous? Which columns?

Ditto rows?

Vwhee
05-24-2012, 01:33 AM
Rows and Columns are both contiguous - at the moment I'm using H6:O10000

Bob Phillips
05-24-2012, 05:15 AM
In my code, change


Set rng = .UsedRange

to


Set rng = .Range("H6:O10000")

Vwhee
05-24-2012, 05:51 AM
Thanks xld - I pretty much tried every variation except that!!! I'll give it a go :)