Macro to find value greater than 0

[slohman]

I need help I have this macro and I would like it to find greater than zero at the moment it is value <> as I have large spreadsheet that has zero as well it is picking up those as well.

[pre]

Private Sub CommandButton1_ClickXXX()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
Dim i As Long
Dim MyCol As Integer
Dim MyRow As Integer

LR = Sheets(SheetName).Range("BY" & Rows.Count).End(xlUp).Row
MyCol = 3
MyRow = 27
For i = 118 To 561
If Sheets(SheetName).Range("BZ" & i).Value <> "" Then
' skip non blanks - start
Do Until Sheets("Installer").Cells(MyRow, MyCol).Value = ""
If MyRow = 52 Then
MyCol = 16
MyRow = 27
Else
MyRow = MyRow + 1
End If
Loop
' skip non blanks - end

Sheets("Installer").Cells(MyRow, MyCol).Value = Sheets(SheetName).Range("BZ" & i).Value
MyRow = MyRow + 1
End If
If MyRow = 52 Then
If MyCol = 3 Then
MyCol = 16
MyRow = 27
Else
MsgBox "You have ran out of room.  Some entries were not copied"
Exit For
End If
End If
Next i

End Sub

[/pre]

 

[bobhc]

Good day slohman


Would this line do what you want


If Sheets(SheetName).Range("BZ" & i). Value > 0 Then

 

[slohman]

Is there another way to copy it seems to be picking if anything is in the cell regardless if it is a number or text it should only be copying if number is greater than zero.

 

[NARAYANK991]

Hi ,


You can use :

[pre]

If Val(Sheets(SheetName).Range("BZ" & i).Value) > 0 Then

[/pre]
Narayan