Macro to convert rows with tonnes inputs to kilograms

[lekh0602]

Hi,

I have specific columns where I input values manually - the rows are constant so always from B11 to Q38 and down to row 72 (B-Q). The inputs are values in tonnes that I would like to convert to kilograms by adding three 0. However, some of the cells are also text cells which should not have the three 0s added. Will it be possible to create a macro that auto fill every number cell with "000" but not to any text cell?

Any assistance in this matter is highly appreciated.

Thanks!

 

[VoG]

You don't need a macro.

Enter 1000 in an empty cell and copy it.

Select the data, Edit > Paste Special, tick Multiply and click OK.

Text cells will be ignored.

 

[lekh0602]

Thanks, VoG

Since all cells will be filled with values in tonnes I would like just one button to convert from tonnes to kilograms, since the people using the sheet are not very Excel savvy. That's why I would like to minimise the number of actions they need to do.

Do you think a macro will be able to do that?

 

[VoG]

Try

Sub Tonnes()
With Range("IV1")
    .Value = 1000
    .Copy
End With
Range("B11:Q72").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
End Sub

 

[lekh0602]

Thank you very much, VoG - I just tried to insert the code like this:

Sub FoecastDI_Button1_Click()


Sub Tonnes()
With Range("IV1")
.Value = 1000
.Copy
End With
Range("B11:Q72").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply


End Sub

BUT I get a "Compile error: Expected End Sub"?

 

[VoG]

Try

Sub FoecastDI_Button1_Click()
With Range("IV1")
    .Value = 1000
    .Copy
End With
Range("B11:Q72").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
End Sub

 

[Gilliam]

I would probably add this line of code between the end with and Range("B11.......

    Range("B11:Q72").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select

which only selects the numbers

 

[lekh0602]

THANKS! That works now but can it multiply only the cells where the values have been changed? Otherwise it keeps multiplying with a 1000 to all cells increasing the sum too much.

 

[VoG]

Right click the sheet tab and select View Code then paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("B11:Q72")) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Target
        If IsNumeric(c.Value) Then c.Value = c.Value * 1000
    Next c
    Application.EnableEvents = True
End If
End Sub

Values will be converted as they are entered.

 

[lekh0602]

Thanks a lot, VoG - that works perfectly.

Can I insert the same rode to another range but in the same worksheet? I have two tables in one sheet and one range is B11:Q72 and the other is B11:Q30?