Next available row

dreimer

New Member
Joined
May 13, 2019
Messages
11
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,557
Office Version
  1. 365
Platform
  1. Windows
Re: Next avaialable row

Hi & welcome to MrExcel.
There is nothing obviously wrong with that code, which line is highlighted when you click Debug?
 

dreimer

New Member
Joined
May 13, 2019
Messages
11
Re: Next avaialable row

Range("A" & irow) = DemographicPatientName.Value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,557
Office Version
  1. 365
Platform
  1. Windows
Re: Next avaialable row

What is the value of irow?
 

dreimer

New Member
Joined
May 13, 2019
Messages
11

ADVERTISEMENT

Re: Next avaialable row

range is where the error is but irow is empty
 

dreimer

New Member
Joined
May 13, 2019
Messages
11
Re: Next avaialable row

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

in the editor when I hover the cursor over Range the display box reads
"Method Range of Object Global Failed"
when I hover over irow, the box reads "empty"
hovering over the
DemographicPatientName.Value - the value I entered is there
<strike>
</strike>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,557
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: Next avaialable row

irow cannot be empty, if you add the message box as shown, what does it say
Code:
Private Sub CB1Save_Click()
Sheets("Sheet2").Activate
'Save to Worksheet
Dim irow As Long
irow = Range("B" & Rows.Count).End(xlUp).Row + 1
[COLOR=#ff0000]MsgBox irow[/COLOR]
Range("A" & irow) = DemographicPatientName.Value
 

dreimer

New Member
Joined
May 13, 2019
Messages
11
Re: Next avaialable row

sorry that was an incomplete answer
the message box reads "Last Row 3"
because i have 2 rows of data already
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,557
Office Version
  1. 365
Platform
  1. Windows
Re: Next avaialable row

What code did you use to get that result?
The code I supplied should just have given a number.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,556
Messages
5,637,035
Members
416,954
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top