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

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,101
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this.
Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this?

Code:
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

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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

Well-known Member
Joined
Sep 22, 2002
Messages
1,064

ADVERTISEMENT

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
BCDEFGHI
2NumberDateTransaction DescriptionPayment/Debit (-)CodeFee (-)Deposit/Credit (+)$966.00
312/16/2005Gas$6.00-$6.00
4Emmy$960.00
512/16/2005H-E-B$24.37-$24.37
6$935.63
712/16/2005XXXXX$1,954.19$1,954.19
8$2,889.82
912/16/2005Verizon$107.21-$107.21
10$2,782.61
1112312/16/2005Capitol One Auto Finance.$279.89-$279.89
12$2,502.72
1312212/16/2005Emmy's Car$280.00-$280.00
14$2,222.72
1512/16/2005KFC$3.85-$3.85
16$2,218.87
17 
18 
19 
20 
21 
22 
23 
Transaction Register
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,101
Office Version
  1. 365
Platform
  1. Windows
Sorry my code has an error, the Text property of a cell is read only.:oops:
Code:
Dim LastRow As Long 

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

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
Andrew

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
dbmathis said:
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,264
Messages
5,577,076
Members
412,763
Latest member
sienweiw
Top