Vba for formatting cell

[Alpacino]

Hi again
I would like a vba to convert values from a range Z4:Z35 then copy to C4:C35
Say the value in Z4 is 34 I like to multiple the value by 1000 then copy new format to C4.

At the moment I have to do a sum formula into Y4 =SUM(Z4*1000) then copy values to C4. I just want to bypass this painful way of doing it

Thanks for ur help
Alan

 

[id107]

You could just put "=Z4*1000" into C4 or you could use something like

Sub ZtoC()
    With Range("C4:C35")
        .Formula = "=Z4*1000"
        .Value = .Value
    End With
End Sub

 

[Alpacino]

I forgot to mention I use a for next loop with a vlookup formula as follows:
"sales" is a range 2 columns with y being names same as column A and z column.

Sub vlookupSales

Dim I as long
For I = 4 to 35

Cells(I , 3).formulaR1C1 = application.worksheetfunction.vlookup(cells(I,1),range("sales"),2,0)
Next I
End sub

How do I add this to the sub above. I use this sub straight after I use tp format the z column.

 

[id107]

Your Vlookup seems to be done in C4:C35, the same place that the Col Z*1000 results now are.

Is that right?

 

[Alpacino]

Yeah that's right what I do is format the values in z column to y then results from vlookup in column c

 

[id107]

Ahh. What's the formatting in Col Y?

 

[Alpacino]

Well the y column would be =sum(z4*1000)
A bit more detail z column has figures in 000's shortened
Eg z4 = 34 k I want to format this To 34000.
The source of z values is from another workbook and the names and values are in different order to my sheet. That's why I'm using vlookup to reorder the values to the right name in column c.

 

[Rick Rothstein]

If Z4 has the text constant "34 k" (without the quote marks) in it, then you can use this formula to return 34000...

=1000*LEFT(Z4,FIND(" ",Z4)-1)

Although if you plan to copy the formula down, possibly through blank cells in Column Z (in anticipation of more entries being made in the future), then you would probably want this version of the formula...

=IF(Z4="","",1000*LEFT(Z4,FIND(" ",Z4)-1))

However, I just wanted to point out to you in case you can make use of this, there is a way to have the cell value be 34000 while it displays as "34 k"; simply Custom Format the cell using this format pattern...

0," k"

 

[Alpacino]

Thanks for that the values in z column just has numbers in them.

 

[Alpacino]

Could u vlookup the values to column c first and then format the values *1000 in column c