Help with Varible in Macro

  • Thread starter Thread starter simonc
  • Start date Start date
S

simonc

Hello,

Can anyone help please,

I have three cols each with seperate numbers in them depending on wha
number range it is in each col, it represents a low, med or high.

I am trying to gauge what this is and setting it to a varible or w
seebelow and then paste what it set w$ to a cell.

Can anyone see what is wrong, As you can see not Im not the best a
doing this stuff.

:confused:

Sub Totals()
'
Let w$ = "blank"
Let gl$ = 0
Let gm$ = 0
Let gh$ = 0

Let al$ = 0
Let am$ = 0
Let ah$ = 0

Let crl$ = 0
Let crm$ = 0
Let crh$ = 0

Range("B2").Select
Let g$ = ActiveCell
Range("C2").Select
Let A$ = ActiveCell
Range("D2").Select
Let CR$ = ActiveCell

If g$ <= 18 Then Let w$ = "L"
If g$ >= 19 <= 25 Then Let w$ = "M"
If g$ >= 26 Then Let w$ = "H"
Range("G1").Select

ActiveCell.Paste (w$
 
One way:

Public Sub Totals()
Dim w As String

Select Case Range("B2").Value
Case Is < 18
w = "L"
Case Is <= 25
w = "M"
Case Else
w = "H"
End Select
Range("G1").Value = w

Couple of things:

1) You almost never need to select anything - using the range object
directly (e.g., Range("G1").Value = w) makes your code smaller, faster,
and IMO easier to maintain

2) You can only Paste something copied to the clipboard. However, as
above, you can assign a value directly to a cell's Value property

3) The "Let" operator is almost never used any more, though it's still
perfectly valid syntax. Instead, the "Let" is implied in

g = ActiveCell.Value

4) Using the "$" in g$ coerces the value of ActiveCell to a string. Is
that what you intended? It appears not, given that you then compare g to
a number.
 
Back
Top