VBA Help

[coliervile]

The following code is suppose to unload data from a VBA form into the first empty cell in column A and in columns A, B, E, H, K, and N...but it is not doing that. I'm racking my brain here and can't fiure out why...every have one of these days???

Thanks for looking and your help-
Charlie

Here is the header:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	G	$ Change	% Change	F	$ Change	% Change	C	$ Change	% Change	S	$ Change	% Change	I	$ Change	% Change	Best	Worst
Funds

Last Row:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
4095	26-Apr-11	13.6146	0.0011	0.0001	14.4436	0.0303	0.0021	16.3808	0.1457	0.0090	23.3091	0.2180	0.0094	21.4651	0.1071	0.0050	S	G
4096	27-Apr-11	13.6158	0.0012	0.0001	14.4218	(0.0218)	(0.0015)	16.4861	0.1053	0.0064	23.4610	0.1519	0.0065	21.7507	0.2856	0.0133	I	F
4097	28-Apr-11	13.6169	0.0011	0.0001	14.4597	0.0379	0.0026	16.5459	0.0598	0.0036	23.5252	0.0642	0.0027	21.8792	0.1285	0.0059	I	G
4098	29-Apr-11	13.6192	0.0023	0.0002	14.4751	0.0154	0.0011	16.5841	0.0382	0.0023	23.6175	0.0923	0.0039	21.9484	0.0692	0.0032	S	G
4099	*	*	(13.6192)	(1.0000)	*	(14.4751)	(1.0000)	*	(16.5841)	(1.0000)	*	(23.6175)	(1.0000)	*	(21.9484)	(1.0000)	G	G
Funds

Form Coding:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Funds")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 0).Row

'check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 0).Value = Me.txtDate.Value
ws.Cells(iRow, 1).Value = Me.txtG.Value
ws.Cells(iRow, 4).Value = Me.txtF.Value
ws.Cells(iRow, 7).Value = Me.txtC.Value
ws.Cells(iRow, 10).Value = Me.txtS.Value
ws.Cells(iRow, 13).Value = Me.txtI.Value

'clear the data
Me.txtDate.Value = ""
Me.txtG.Value = ""
Me.txtF.Value = ""
Me.txtC.Value = ""
Me.txtS.Value = ""
Me.txtI.Value = ""
Me.txtDate.SetFocus

End Sub

 

[coliervile]

I'm usng this coding to find the first empty cell in Column A (see below). It seem to select the first empy cell in Column A but, I get the following "Run-time error '1004'". When I Debug it this line is highlighted "ws.Cells(iRow, 0).Value = Me.txtDate.Value" and I can't figure this out now!!!

Charlie

If Application.WorksheetFunction.CountA("A:A") = 0 Then
[A1].Select
Else
On Error Resume Next
Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Select
If Err <> 0 Then
On Error GoTo 0
[A65536].End(xlUp)(2, 1).Select
End If
On Error GoTo 0
End If

 

[Norie]

Change 0 to 1 or "A".

Cells(iRow ,1)

You'll probably need to change the column for the rest of the Cells.

You can use the column number or the column letter(s).
I'd suggest you use the latter in this case so you don't need to work out the number.

 

[coliervile]

If the first empty cell in Column A (Date) was custom formatted as d-mmm-yy would this cause the run-time error since the form is trying to download it as "txtDate"? If this were the case how would I go about changing the user form vba to make it enter the date in the custom format?

Charlie

 

[coliervile]

Thank you Norie for your suggestion. I tried doing it both ways using the alphanumerics and numbers, A, B, E, H, and N AND 1, 2, 5, 8, 11, and 14. I get the same run-time error message.

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.txtGFund.Value
ws.Cells(iRow, 5).Value = Me.txtFFund.Value
ws.Cells(iRow, 8).Value = Me.txtCFund.Value
ws.Cells(iRow, 11).Value = Me.txtSFund.Value
ws.Cells(iRow, 14).Value = Me.txtIFund.Value

Charlie

 

[Norie]

Charlie

I'm a little confused.

In your first code you appear to be getting the value for iRow with one line of code but them you seen to say your doing it with a fairly large chunk of code.

 

[coliervile]

Norie I did change it. I was thinking that was the part of the coding that was the problem. I changed the iRow back to its original coding. After changing the coding back the data off of the user form does go into the proper columns but it puts the new data in the last row filled row (4098) instead of the first empty row (4099), in the example in post #1.

Charlie

 

[Norie]

That's because the Offset you use when working out iRow is wrong.

It should be Offset(1, 0).

 

[coliervile]

Thank you Norie it seems to be working as I want it to. It gets frustrating when you can't see the problem and I'm the problem...ha ha ha.

Thanks again,
Charlie