VBA: Multiply Entire Column by a Number

[legalhustler]

I would like to multiply the values in column K by .05. I tried something like this but says invalid use of property.

Set TBSheet = Sheets("TestBefore")
TBSheet.Range ("K")
.Value = Evaluate(.Address & "*.05")

P.S. I'm not sure what .address above indicates.

 

[legalhustler]

Also tried the following but did not work:

Set TBSheet = Sheets("TestBefore")
TBSheet.Range ("K").Value *.05

 

[Norie]

Try this.

With TBSheet.Range("K:K")
    .Value = Evaluate("INDEX(" & .Address & "*.05,,1)")
End With

PS That code will multiply every cell in the column, even empty cells which will end up with 0 in them.

 

[mikerickson]

You need to use the correct address for a full column as well as the With construction

    With Range("K:K")
        .Value = Evaluate(.Address & "*.05")
    End With

 

[Sandeep Warrier]

Hi,

Enter 0.05 in some unused cell, M1 for example.

    Range("M1").Copy
    Range("K1:K100").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply

Change references to match your data.

 

[legalhustler]

Try this.

With TBSheet.Range("K:K")
    .Value = Evaluate("INDEX(" & .Address & "*.05,,1)")
End With

PS That code will multiply every cell in the column, even empty cells which will end up with 0 in them.

Ok - How can I have it start from K2 to the last row of data?

 

[legalhustler]

I was able to get it to start from K2, but it multiplies past the last row still. Not sure what I'm doing wrong. Here is the revised code:

ith TBSheet.Range("K2", TBSheet.Cells(Rows.Count, "K").End(xlDown))
    .Value = Evaluate("INDEX(" & .Address & "*.01,,1)")
    End With

 

[legalhustler]

I was able to get it to start from K2, but it multiplies past the last row still. Not sure what I'm doing wrong. Here is the revised code:

ith TBSheet.Range("K2", TBSheet.Cells(Rows.Count, "K").End(xlDown))
    .Value = Evaluate("INDEX(" & .Address & "*.01,,1)")
    End With

Nevermind, resolved issue. Should be xlUp in code.

But there is a related issue: http://www.mrexcel.com/forum/excel-...ying-number-format-correctly.html#post4091333