VBA help: Userform: On click search up column and paste.

[dbmathis]

Hi all,

I have created a userform (transEntry) and I am seeking an example of taking the data that has been entered into a textbox2 on the userform and searching up copying it into a cell on my sheet.

The Submit button on the userforn is: CommandButton1
An example text box is: TextBox2
There is a column of data, cells I2 thru I212, and cells I2 thru I100 contain data.

What I am hoping I can get to happen is there will be text in TextBox2 and when someone clicks the Submit button (CommandButton1), to start in cell I212 and search upward until a cell with data in it's found (in this example: I100) and then paste the data that's in Textbox2 six cell to the left of it (which would be in this example C100)?

I thought this was a fairly easy thing to do, but I tried and could not get it done yet. Any help would be appreciated.

Thanks

 

[Norie]

Perhaps something like this.

Dim LastRow As Long

LastRow = Range("I" & Rows.Count).End(xlUp).Row

Range("C" & LastRow).Text = TextBox2.Text

Which would go in the Submit button's click event.

 

[Andrew Poulsom]

Like this?

Private Sub CommandButton1_Click()
    Worksheets("Sheet1").Range("I212").End(xlUp).Offset(0, -6).Value = TextBox2.Value
End Sub

Change the sheet reference to suit.

 

[dbmathis]

Hmm, thanks to both of you, I actually tried what andrew suggested yesterday and it would not work.

Maybe I typed a character wrong. I will try it again when I get to the office.

 

[dbmathis]

I ran andrews code on the following sheet and it put the data in cell C2? Should it have changed cell C16 instead?

I tried Norie's code and I got the following error: Runtime Error '424': Object required.

Transaction Register Starting Dec-16-2005 Draft.xls
	B	C	D	E	F	G	H	I
2	Number	Date	Transaction Description	Payment/Debit (-)	Code	Fee (-)	Deposit/Credit (+)	$966.00
3		12/16/2005	Gas	$6.00				-$6.00
4			Emmy					$960.00
5		12/16/2005	H-E-B	$24.37				-$24.37
6								$935.63
7		12/16/2005	XXXXX				$1,954.19	$1,954.19
8								$2,889.82
9		12/16/2005	Verizon	$107.21				-$107.21
10								$2,782.61
11	123	12/16/2005	Capitol One Auto Finance.	$279.89				-$279.89
12								$2,502.72
13	122	12/16/2005	Emmy's Car	$280.00				-$280.00
14								$2,222.72
15		12/16/2005	KFC	$3.85				-$3.85
16								$2,218.87
17
18
19
20
21
22
23
Transaction Register

 

[Norie]

Sorry my code has an error, the Text property of a cell is read only.

Dim LastRow As Long 

LastRow = Range("I" & Rows.Count).End(xlUp).Row 

Range("C" & LastRow).Value = TextBox2.Text

 

[Andrew Poulsom]

I ran andrews code on the following sheet and it put the data in cell C2? Should it have changed cell C16 instead?

It will do that if C17:C212 isn't empty.

 

[dbmathis]

Andrew

Yeah, there are formuals in the cells, but I thought the VBA ignores the formulas?

 

[Andrew Poulsom]

Andrew

Yeah, there are formuals in the cells, but I thought the VBA ignores the formulas?

The End method does not treat cells containing "" as blank cells. But AutoFilter does, so try:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    With Worksheets("Sheet1").Range("I2:I212")
        .AutoFilter Field:=1, Criteria1:="<>"
        .Cells(1, 1).End(xlDown).Offset(0, -6).Value = TextBox2.Value
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub