Chefsohail
Board Regular
- Joined
- Oct 3, 2020
- Messages
- 90
- Office Version
- 365
- Platform
- Windows
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
I want the following to stay as formulae and not change..
Just the others to paste Values and text format.
Fluff had given a resolution as below for the first line of code.
to be replaced 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.
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
VBA Code:
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..
VBA Code:
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.
VBA Code:
wsDS.Range("B2").Formula = "=LEFT(A2,8)": wsDS.Range("B2:B" & wsDS.UsedRange.Rows.Count).FillDown
to be replaced with
VBA Code:
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.