vba code to convert to number

[Patriot2879]

HI I have where i want Range AP2 to last column to convert to number please can you help?

Private Sub CommandButton1_Click()

  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("Jun")
  Set pasteSheet = Worksheets("Jun")
  
  Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  On Error Resume Next
  Range("AJ2:AJ" & Lastrow).SpecialCells(xlBlanks).Value = Range("AJ2").Value
  On Error GoTo 0
Range("AP2", Range("AP2").End(xlDown)).Copy Range("A2")
Range("A2", Range("A2").End(xlDown)).NumberFormat = "0"


End Sub

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()


  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("Jun")
  Set pasteSheet = Worksheets("Jun")
  
  Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  On Error Resume Next
  Range("AJ2:AJ" & Lastrow).SpecialCells(xlBlanks).Value = Range("AJ2").Value
  On Error GoTo 0

[COLOR=#0000ff]    Set r = Range("AP2", Range("AP" & Rows.Count).End(xlUp))[/COLOR]
[COLOR=#0000ff]    Range("A2").Resize(r.Rows.Count).Value = r.Value[/COLOR]



End Sub

 

[Patriot2879]

Thank you I shall try this on Monday

 

[DanteAmor]

Youre welcome, let me know if you have any question.

 

[Patriot2879]

this works fab many thanks for your help on this

 

[Patriot2879]

Hi good morning, for some reason now the code below doesn't go to the last row, please can you advise?

Range("C2:C" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:D,3,FALSE)"
Range("B2:B" & Lastrow).Formula = "=IF(A2=A1,0,1)"
Range("D2:D" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:E,4,FALSE)"

 

[Patriot2879]

hi and for some reason the code is going into C1, B1 and D1, and not into C2, B2 and D2 as in the code, I hope you can help me please.

below is the full code

Private Sub CommandButton1_Click()
 Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Set copySheet = Worksheets("Jun")
  Set pasteSheet = Worksheets("Jun")
  
  Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  On Error Resume Next
  Range("AJ2:AJ" & Lastrow).SpecialCells(xlBlanks).Value = Range("AJ2").Value
  On Error GoTo 0
    Set r = Range("AP2", Range("AP" & Rows.Count).End(xlUp))
    Range("A2").Resize(r.Rows.Count).Value = r.Value
Range("AB2", Range("AB2").End(xlDown)).Copy Range("E2")
Range("AD2", Range("AD2").End(xlDown)).Copy Range("F2")
Range("AO2", Range("AO2").End(xlDown)).Copy Range("G2")
Range("AG2", Range("AG2").End(xlDown)).Copy Range("H2")
Range("AJ2", Range("AJ2").End(xlDown)).Copy Range("I2")
Range("AS2", Range("AS2").End(xlDown)).Copy Range("M2")
Range("C2:C" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:D,3,FALSE)"
Range("B2:B" & Lastrow).Formula = "=IF(A2=A1,0,1)"
Range("D2:D" & Lastrow).Formula = "=VLOOKUP(A2,Apr!A:E,4,FALSE)"
End Sub

 

[Patriot2879]

Hi, I hope you can help, I am really confused why the data is not going into the correct cells.

 

[DanteAmor]

Could you explain what you need?

 

[Patriot2879]

Hi I got it working now many thanks for your help much appreciated. I had a couple of codes in the wrong place. Thanks again for your help