Vba that deals with noncontiguous range

[twl2009]

Hi,

I have the simple bit of code below, that add or removes a percentage of cells value. I need to apply this to quite a large number of cells that wont be a contiguous range, is there an easy way of doing this with writing the same line many times?

Sub SalesSeasons()
       Worksheets("SalesForecast").Range("H17") = Range ("H17").Value*Range("X6").Value
End Sub

 

[My Aswer Is This]

Is there some logic to what ranges?

 

[twl2009]

Not really they are individual cells spread about the sheet.

 

[footoo]

Sub ft()
Dim rng As Range, cel As Range
Set rng = [W2:W4,W6:W7] 'change as required
For Each cel In rng
    cel = cel * [X6]
Next
End Sub

 

[JackDanIce]

Just to add to footoo's suggestion, you can write non-contiguous range's as:

Range("A2, F6, K2:L4")

where non-contiguous cells are separated by comma's, but colon is used where there is a contiguous range, even if it's non contiguous to another range
If you then loop over this range, it would consider, in this order, cells:

A2
F6
K2
K3
K4
L2
L3
L4

Using square brackets ( "[" and "]") or Range or Cells when defining ranges, for here at least, just different syntax options or ways to reference a range object. They have their pro's and con's which you can look up if so.

 

[Peter_SSs]

A variation of footoo's suggestion would be to select all the cells on the worksheet (Ctrl+click) and make that a named range (eg "RangeToMultiply") then the code would be something like

Sub ft2()
  Dim cel As Range
  
  For Each cel In Range("RangeToMultiply")
      cel.Value = cel.Value * Range("X6").Value
  Next cel
End Sub

 

[My Aswer Is This]

If you have no logic then you will have to do something like footo used.

But this would mean writing a lot of code if you have lots of cells that need this.

If this is only a one time thing you will be doing it would be easier to use the below script.

Double click on the cell you want this to work on. The value you double click on will be increased when you double click on the cell.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Value = Target.Value * Range("X6").Value
End Sub

 

[twl2009]

Thanks all, thats very helpful

 

[Peter_SSs]

Thanks all, thats very helpful

You are very welcome. Lots of choices!

 

[twl2009]

One question though, once you have set a named range how do you change the cells assigned to it?