rayoconnell
05-15-2007, 06:23 AM
Hello All,
im trying to count how many lines are in my spreadsheet( this bit works)
then when i have that count i am trying to apply that count to the end of my "copy function" the 3rd last line"Range("C2:CintNosRows")".
i have dimed the count as intNosRows and tryed to put that in instead of a constant number due to the fact every sheet varys in size.
As usual any help at all would be great!!!!!!
Select
Private Sub CommandButton8_Click()
' Macro1 Macro
' Macro recorded 5/15/2007 by Ray O'Connell
'
'
Dim intNosRows As Integer, CellAddress As String, Strbuffer As String, i As Integer
Strbuffer = CStr(Range("A1").Value)
i = 1
'Find how far we have to go
Do
If Strbuffer <> vbNullString Then
intNosRows = intNosRows + 1
End If
i = i + 1
CellAddress = "A" & CStr(i)
Strbuffer = CStr(Range(CellAddress).Value)
Loop Until Strbuffer = vbNullString
'Reset counter
i = 0
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[5]=""Y"",""VESSEL"")"
ActiveCell.FormulaR1C1 = "=IF(RC[5]=""Y"",""VESSEL"")"
Range("C2").Select
Selection.Copy
Range("C2:CintNosRows").Select
ActiveSheet.Paste
End Sub
im trying to count how many lines are in my spreadsheet( this bit works)
then when i have that count i am trying to apply that count to the end of my "copy function" the 3rd last line"Range("C2:CintNosRows")".
i have dimed the count as intNosRows and tryed to put that in instead of a constant number due to the fact every sheet varys in size.
As usual any help at all would be great!!!!!!
Select
Private Sub CommandButton8_Click()
' Macro1 Macro
' Macro recorded 5/15/2007 by Ray O'Connell
'
'
Dim intNosRows As Integer, CellAddress As String, Strbuffer As String, i As Integer
Strbuffer = CStr(Range("A1").Value)
i = 1
'Find how far we have to go
Do
If Strbuffer <> vbNullString Then
intNosRows = intNosRows + 1
End If
i = i + 1
CellAddress = "A" & CStr(i)
Strbuffer = CStr(Range(CellAddress).Value)
Loop Until Strbuffer = vbNullString
'Reset counter
i = 0
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[5]=""Y"",""VESSEL"")"
ActiveCell.FormulaR1C1 = "=IF(RC[5]=""Y"",""VESSEL"")"
Range("C2").Select
Selection.Copy
Range("C2:CintNosRows").Select
ActiveSheet.Paste
End Sub