VBA Userform To Excel Inquiry!
 

Hiya all!

I have a userform setup in Excel 97 VBA and I want to transfer the
inputed data into a specific row in Excel. Initially that may sound
rather simple but here's what I believe to be the tough part. I want
the inputed data to look through the target spreadsheet and tranfer
all data from the userform based on an inputed date. This date and
all other data should all go into the specific row that fits the date.
Basically, the date should be implemented based on chronological
order.

For example, if the date entered was 5/22/04, it should be inserted
between existing entries of 5/21/04 and 5/23/04. The rest of the data
should follow it in the same row.

Now even that would be somewhat simple by the use of the sort command,
however I have subtotals at the end of each month in the spreadsheet.
The macro in question would have to insert a row in the proper area
and then import the data.

Am I crazy to think that such a scenario is possible?

Thanks in advance!

Kris Taylor
www.QuestOfAges.org Administrator

VBA Userform To Excel Inquiry!
 

Dim rng as Range
Dim dt as Date

set rng1 = Nothing
set rng = Range(cells(1,1),Cells(rows.count,1).End(xlup))
dt = cdate(Textbox1.Text)
for each cell in rng
if isdate(cell) then
if cell.Value = dt then
msgbox "Date already exist"
elseif cell.Offset(1,0).Value dt then
cell.offset(1,0).EntireRow.Insert
set rng1 = cell.offset(1,0)
exit for
end if
end if
Next
if not rng1 is nothing then
' use rng1 to see where to write you data
End if

The above pseudo code should give you some idea of how to approach it. You
will have to add extra code to check for boundary conditions (such as the
1st of the month/last of the month and so forth).

--
Regards,
Tom Ogilvy



"Kris Taylor" wrote in message
om...
Hiya all!

I have a userform setup in Excel 97 VBA and I want to transfer the
inputed data into a specific row in Excel. Initially that may sound
rather simple but here's what I believe to be the tough part. I want
the inputed data to look through the target spreadsheet and tranfer
all data from the userform based on an inputed date. This date and
all other data should all go into the specific row that fits the date.
Basically, the date should be implemented based on chronological
order.

For example, if the date entered was 5/22/04, it should be inserted
between existing entries of 5/21/04 and 5/23/04. The rest of the data
should follow it in the same row.

Now even that would be somewhat simple by the use of the sort command,
however I have subtotals at the end of each month in the spreadsheet.
The macro in question would have to insert a row in the proper area
and then import the data.

Am I crazy to think that such a scenario is possible?

Thanks in advance!

Kris Taylor
www.QuestOfAges.org Administrator