VBA code to fill values and text format

[Chefsohail]

Hi @Fluff and Team,

Please help me with the below vba code. I am able to achieve the following - 1. Add formula to the cell and 2. Fill Formula down... What i achieved is that the formula is filled down as required. But i do not want the formula here. I want its corresponding value to appear and the cell to be in text format as there are preceeding zeros and i dont want to loose them. The vlookup in the code below looks up those values from the other sheet. Please help

wsDS.Range("B2").Formula = "=LEFT(A2,8)": wsDS.Range("B2:B" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("D2").Formula = "=RIGHT(C2,5)": wsDS.Range("D2:D" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("E2").Formula = "=VLOOKUP(D2,'UOM Comm'!D:R,2,0)": wsDS.Range("E2:E" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("F2").Formula = "=VLOOKUP(D2,'UOM Comm'!D:R,7,0)": wsDS.Range("F2:F" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("i2").Formula = "=LEN(H2)": wsDS.Range("i2:i" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("k2").Formula = "=LEN(J2)": wsDS.Range("k2:k" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("P2").Value = Format(Date, Format:="mm/dd/yy"): wsDS.Range("P2:P" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("R2").Formula = "=IF(M2="""",""Failure"", IF(LEFT(J2,1)=CHAR(41),""Na"",""Auto Approve""))": wsDS.Range("R2:R" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("O2").Value = "Description New Task": wsDS.Range("O2:O" & wsDS.UsedRange.Rows.Count).FillDown

I want the following to stay as formulae and not change..

wsDS.Range("i2").Formula = "=LEN(H2)": wsDS.Range("i2:i" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("k2").Formula = "=LEN(J2)": wsDS.Range("k2:k" & wsDS.UsedRange.Rows.Count).FillDown

Just the others to paste Values and text format.

Fluff had given a resolution as below for the first line of code.

wsDS.Range("B2").Formula = "=LEFT(A2,8)": wsDS.Range("B2:B" & wsDS.UsedRange.Rows.Count).FillDown

to be replaced with

With wsDS.Range("A2", wsDS.Range("A" & Rows.Count).End(xlUp))
      .Offset(, 1).Value = Evaluate("if({1},left(" & .Address & ",8))")
   End With

This worked. But it changes the format and the preceeding zero's are lost. I am OK with this one... But for the other line of codes, i need them. Thanks.

 

[Marc L]

Hi,​

​

after the Range.Formula = … and the Range.FillDown - as FillDown could be avoided with a formula directly on all cells of the range … -​

​

just add a Range.Formula = Range.Value2 codeline …​

 

[Chefsohail]

Hey @Marc L ,

Thanx for your assistance.

I tried to replace -

wsDS.Range("D2").Formula = "=RIGHT(C2,5)": wsDS.Range("D2:D" & wsDS.UsedRange.Rows.Count).FillDown

with

wsDS.Range("D2").Formula = "=RIGHT(C2,5)": wsDS.Range("D2:D" & wsDS.UsedRange.Rows.Count).FillDown
wsDS.Range("D2:D" & wsDS.UsedRange.Rows.Count).Formula = wsDS.Range("D2:D" & wsDS.UsedRange.Rows.Count).Value2

However the first value i.e D2 is copied untill the last row.

When i tried replacing with

wsDS.Range("D2").Formula = "=RIGHT(C2,5)": wsDS.Range("D2:D" & wsDS.UsedRange.Rows.Count).Value2

received Complie error: Invalid use of Property.

I am sure I must have made a mistake. Please advice.

 

[Marc L]

    With wsDS.Range("D2:D" & wsDS.UsedRange.Rows.Count)
        .Formula = "=RIGHT(C2,5)"
        .Formula = .Value2
    End With

 

[Chefsohail]

Thanx @Marc L ,

With this the value is in place now. But the preceeding zeros are lost.

 

[Chefsohail]

Any idea how to ensure that preceding zero's are not lost

 

[Alex Blakenburg]

Try adding the line below before the .Value2 line.
This will format the cells as text.

        .NumberFormat = "@"

Alternatively if you know its always going to be 5 characters and you want to keep it as a number just use:-

        .NumberFormat = "00000"