Next available row

[dreimer]

Please help before I go mad.

I am working on a patient assessment form - there's going to be approximately 80-90 columns of data in each row when it's done. The first part is patient demographics. All I am trying to do is get the next available row. I can't get past the run time error. "Method Range of Object Global Failed




Private Sub CB1Save_Click()
Sheets("Sheet2").Activate
'Save to Worksheet
Dim irow As Long
irow = Range("B" & Rows.Count).End(xlUp).Row + 1

Range("A" & irow) = DemographicPatientName.Value
Range("B" & irow) = DemographicDOB.Value
Range("C" & irow) = DemographicAge.Value
Range("D" & irow) = DemographicMonitor.Value
Range("E" & irow) = DemographicDeviceNumber.Value
Range("F" & irow) = DemographicStartDate.Value
Range("G" & irow) = DemographicEndDate.Value

 

[dreimer]

Re: Next avaialable row

running your code the mesgBox

'lRow = Cells.Find(What:="*", _
' After:=Range("A1"), _
' LookAt:=xlPart, _
' LookIn:=xlFormulas, _
' SearchOrder:=xlByRows, _
' SearchDirection:=xlPrevious, _
' MatchCase:=False).Row
' MsgBox "Last Row: " & lRowwas 4

 

[dreimer]

Re: Next avaialable row

sorry - new to this

your code - the mesgbox read 4
another code i was trying gave me "Last Row 3"
'lRow = Cells.Find(What:="*", _
' After:=Range("A1"), _
' LookAt:=xlPart, _
' LookIn:=xlFormulas, _
' SearchOrder:=xlByRows, _
' SearchDirection:=xlPrevious, _
' MatchCase:=False).Row
' MsgBox "Last Row: " & lRow

 

[BenOaten]

Private Sub CB1Save_Click()


'Save to Worksheet
Dim irow As Long
Dim ws As Worksheet


Set ws = Sheets("Sheet2")


irow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1


ws.Range("A" & irow) = ws.Range("DemographicDOB")
ws.Range("B" & irow) = ws.Range("DemographicDOB")
ws.Range("C" & irow) = ws.Range("DemographicAge")
ws.Range("D" & irow) = ws.Range("DemographicMonitor")
ws.Range("E" & irow) = ws.Range("DemographicDeviceNumber")
ws.Range("F" & irow) = ws.Range("DemographicStartDate")
ws.Range("G" & irow) = ws.Range("DemographicEndDate")


End Sub

 

[Fluff]

Re: Next avaialable row

Can you please post the code that you are currently using?
It's very difficult to help when you change it, without telling us.

Your original code had irow, what you have just posted has lrow

 

[BenOaten]

Private Sub CB1Save_Click()


'Save to Worksheet
Dim irow As Long
Dim ws As Worksheet


Set ws = Sheets("Sheet2")


irow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1


ws.Range("A" & irow) = ws.Range("DemographicDOB")
ws.Range("B" & irow) = ws.Range("DemographicDOB")
ws.Range("C" & irow) = ws.Range("DemographicAge")
ws.Range("D" & irow) = ws.Range("DemographicMonitor")
ws.Range("E" & irow) = ws.Range("DemographicDeviceNumber")
ws.Range("F" & irow) = ws.Range("DemographicStartDate")
ws.Range("G" & irow) = ws.Range("DemographicEndDate")


End Sub

 

[dreimer]

Re: Next avaialable row

Here's where its at - now
Private Sub CB1Save_Click()
Sheets("Sheet 2").Activate
Dim lRow As Long
Dim lCol As Long
irow = Range("B" & Rows.Count).End(xlUp).Row + 1
MsgBox irow


Range("A" & irow) = DemographicPatientName.Value
Range("B" & irow) = DemographicDOB.Value
Range("C" & irow) = DemographicAge.Value
Range("D" & irow) = DemographicMonitor.Value
Range("E" & irow) = DemographicDeviceNumber.Value
Range("F" & irow) = DemographicStartDate.Value
Range("G" & irow) = DemographicEndDate.Value
Range("H" & irow) = DemographicPtActivate.Value
Range("I" & irow) = DemographicPtActivateSymp.Value
Range("J" & irow) = DemographicAuto.Value
Range("K" & irow) = DemographicAutoSymp.Value
Range("L" & irow) = DemographicZip.Value
Range("M" & irow) = DemographicLAT.Value
Range("N" & irow) = DemographicCity.Value
Range("O" & irow) = DemographicLONG.Value
Range("L" & irow) = DemographicAddressNow.Value
Range("M" & irow) = DemographicState.Value

 

[dreimer]

Ok - I deleted my first column, and its working!

Thank you

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[dreimer]

No thank you - i just could not get why it wasnt working because i copied it from another form that worked???