userform to add row

hitch_hiker

Active Member
Joined
Feb 21, 2012
Messages
294
I have a list of sequential dates, which I fill data into the corresponding row. I have other events occurring on the same day ( the occurrence, the frequency, and number of events are random - it could be 7 times in one day then nothing for 3 days then 2 or??? ). Currently I manually add a row below the event date and fill data, I want to automate it a bit, I have a userform where I populate the data. I'm up to the point where I need to press "submit" and it goes to the ((date of the event) + 1 ) adds a row and populates it.
currently using a code modified from a suggestion from " Jolivanes " snippet below
Code:
Private Sub CommandButton1_Click()
'sub try this() by Jolivanes
Dim c As Range
Dim datecheck As Date
Dim WorkingDate As Range
datecheck = Range("'report entry'!a8").Value

For Each c In Range("'register report 19_20'!b1:b2000")
    ' need to change 'register report 2018_2019' to match sheet name as per current financial year to suit
  Sheets("register report 19_20").Activate
    ' need to change 'register report 2018_2019' to match sheet name as per current financial year to suit
    If c.Value = datecheck Then
            ' this transfers cells in "report entry" sheet to register report sheet
  Range(c.Address).Select
this works well, I am attempting to modify it. I am thinking something like " if c.Value = datecheck Then c.value =c.Value+1" to get the next row, but I'm certain on how to get the row number, would it be "range(c.address.row).select" or something . ...... Looking for either some suggestions or pointing to some research
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
it will automatically go to the next row after each loop but if you want to select the next row, you can use offset.

c.offset(1,0).select
 
Upvote 0
Thanks Lousymc,
The problem I have is the date is already in the spreadsheet, and is used for entering the daily figures, at a another time (could be earlier or later) , I need to add another row after the first instance of the date to add customer payments, it needs to be after the first instance of the date, and not at the end of the list of dates. Your suggestion doesn't seem to solve my problem,
 
Upvote 0

Forum statistics

Threads
1,214,957
Messages
6,122,466
Members
449,086
Latest member
kwindels

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
Back
Top