Add Formula, fill the row and display the values

[Chefsohail]

HI 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. Please help

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

 

[Chefsohail]

and I have 5 such in the entire vba code...I have other cells with formulae as well.... but i want specific columns to show values and not all. Lets say in this case Range B2:B

 

[Chefsohail]

i may need values and formatting

 

[maabadi]

Try this:

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

 

[Fluff]

Another option

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

 

[Chefsohail]

Hey Thanx @maabadi ,

I tried this...it only copies the value of B2 cell and fills down in the entire column.
Doesnt meet the purpose.

But I do appreciate your help.. Thanx.. Lemme know if I am doing something incorrectly.

 

[maabadi]

Please Check Source data. Maybe 8 character of Source cell are same.

 

[Chefsohail]

Checked @maabadi ...source data is perfect...and I have ensured the first eight characters are different while testing the macro

 

[Fluff]

Have you tried my suggestion?

 

[Chefsohail]

No Fluff,

I am not sure how to add it..Will need your help..

Sub TS_Copy_Columns()
On Error GoTo ErrHand
Application.Calculation = xlManual: Application.ScreenUpdating = False: Application.DisplayAlerts = False

Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsDS As Worksheet, wsData As Worksheet: Set wsDS = wb.Worksheets("DS Tracker"): Set wsData = wb.Worksheets("Data")

' Copy columns
wsData.Range(wsData.UsedRange.Columns("A").Address).Copy wsDS.Columns("A")
wsData.Range(wsData.UsedRange.Columns("B").Address).Copy wsDS.Columns("C")
wsData.Range(wsData.UsedRange.Columns("C").Address).Copy wsDS.Columns("G")
wsData.Range(wsData.UsedRange.Columns("D").Address).Copy wsDS.Columns("H")
wsData.Range(wsData.UsedRange.Columns("E").Address).Copy wsDS.Columns("J")
wsData.Range(wsData.UsedRange.Columns("F").Address).Copy wsDS.Columns("L")
wsData.Range(wsData.UsedRange.Columns("G").Address).Copy wsDS.Columns("M")
wsData.Range(wsData.UsedRange.Columns("H").Address).Copy wsDS.Columns("N")
wsData.Range(wsData.UsedRange.Columns("I").Address).Copy wsDS.Columns("Q")
wsDS.Range("Q2:Q" & wsDS.UsedRange.Rows.Count).FillDown

' Filldown formulas
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


ErrHand:
Application.Calculation = xlAutomatic: Application.ScreenUpdating = True: Application.DisplayAlerts = True
If Err.Number <> 0 Then MsgBox "Something went badly wrong!" & vbCrLf & "VBA-code is ended!" & vbCrLf & vbCrLf & "Error number: " & Err.Number & " " & Err.Description: End
End Sub

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 number format.