find cell

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145
can somebody tell me?

how do i find the last cell used based on a userform entry?

i.e.
if i place the number 8 in a text box on the userform, how do i use that to find the last entry of number 8 on the sheet, and then copy the details from the other text boxes into the cells alongside it.

i am desperate for help on this one so any help greatly appreciated.

cheers
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,311
Office Version
  1. 2016
Platform
  1. Windows
A brief description of your worksheet layout- where the numbers to be found are located in the worksheet... would be helpful.

Regards.
 

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145
columns A to J are in use

with columns A to E already containing data

the number to be found will be in column E

the other entries on the userform need to be placed in columns F to J on the same row as the LAST entry of whatever number is entered in the first text box on the userform.

does that help?

thanks for the assistance
 

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145
Below is the userform code to place the data in Columns A to E

Private Sub cmdadd_Click()

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("bmaddition")

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


'copy data to cells
ws.Cells(irow, 1).Value = Me.datefilled.Value
ws.Cells(irow, 2).Value = Me.startfill.Value
ws.Cells(irow, 3).Value = Me.stopfill.Value
ws.Cells(irow, 4).Value = Me.bmvol.Value
ws.Cells(irow, 5).Value = Me.silono.Value

'clear the data
Me.datefilled.Value = ""
Me.startfill.Value = ""
Me.stopfill.Value = ""
Me.bmvol.Value = ""
Me.silono.Value = ""

End Sub

I have tried to adjust this slightly to add the data to the remaining columns as shown below:

Private Sub cmdadd1_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("bmaddition")

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


'copy data to cells
ws.Cells(irow, 5).Value = Me.silono.Value
ws.Cells(irow, 6).Value = Me.silovol.Value
ws.Cells(irow, 7).Value = Me.prodtype.Value
ws.Cells(irow, 8).Value = Me.mono.Value
ws.Cells(irow, 9).Value = Me.powdertype.Value
ws.Cells(irow, 10).Value = Me.emptydate.Value

'clear the data
Me.silono.Value = ""
Me.silovol.Value = ""
Me.prodtype.Value = ""
Me.mono.Value = ""
Me.powdertype.Value = ""
Me.emptydate.Value = ""
End Sub

What i am getting is the data being posted to the same row each time and overwriting the previous entry, can anybody suggest any changes to achieve what i want?[/b]
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,311
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

See if this works for you :

Code:
Private Sub cmdadd1_Click()

    Private Sub cmdadd1_Click()

    Dim oBottomCell As Range, oSearchRange As Range, oTargetCell As Range
    Dim oWs As Worksheet
    
    Set oWs = Worksheets("bmaddition")
    Set oBottomCell = oWs.Cells(Cells.Rows.Count, 5).End(xlUp)
    Set oSearchRange = oWs.Range(Cells(1, 5), Cells(oBottomCell.Row, 5))
    With oSearchRange
        Set oTargetCell = .Find("ENTER THE NUMBER TO SEARCH FOR HERE !!", SearchOrder:=xlByRows, _
        LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlPrevious)
        If Not oTargetCell Is Nothing Then
            With oTargetCell
                .Offset(, 1) = Me.silono.Value
                .Offset(, 2) = Me.silovol.Value
                .Offset(, 3) = Me.prodtype.Value
                .Offset(, 4) = Me.mono.Value
                .Offset(, 5) = Me.powdertype.Value
                .Offset(, 6) = Me.emptydate.Value
            End With
        End If
    End With
    Me.silono.Value = ""
    Me.silovol.Value = ""
    Me.prodtype.Value = ""
    Me.mono.Value = ""
    Me.powdertype.Value = ""
    Me.emptydate.Value = ""

End Sub

Notice the "ENTER THE NUMBER TO SEARCH FOR HERE !!" string. That's where the number should be placed.


Regards.
 

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145
thanks for the help, but it doesnt seem to be working.

the number to be used will come from the first textbox on the userform so i entered that info into the code, but it keeps throwing up:

run time error 1004
method 'range' of object_'worksheet' failed

and highlights the line shown in bold, any ideas??

Private Sub cmdadd1_Click()

Dim oBottomCell As Range, oSearchRange As Range, oTargetCell As Range
Dim oWs As Worksheet

Set oWs = Worksheets("bmaddition")
Set oBottomCell = oWs.Cells(Cells.Rows.Count, 5).End(xlUp)
Set oSearchRange = oWs.Range(Cells(1, 5), Cells(oBottomCell.Row, 5)) With oSearchRange
Set oTargetCell = .Find(silono.Value, SearchOrder:=xlByRows, _
LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlPrevious)
If Not oTargetCell Is Nothing Then
With oTargetCell
.Offset(, 1) = Me.silono.Value
.Offset(, 2) = Me.silovol.Value
.Offset(, 3) = Me.prodtype.Value
.Offset(, 4) = Me.mono.Value
.Offset(, 5) = Me.powdertype.Value
.Offset(, 6) = Me.emptydate.Value
End With
End If
End With
Me.silono.Value = ""
Me.silovol.Value = ""
Me.prodtype.Value = ""
Me.mono.Value = ""
Me.powdertype.Value = ""
Me.emptydate.Value = ""

End Sub
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,311
Office Version
  1. 2016
Platform
  1. Windows
Sorry there was a slight mistake. The error occurs because the "bmaddition" worksheet is not active when the code runs.

Try this :

Code:
Private Sub cmdadd1_Click()

    Dim oBottomCell As Range, oSearchRange As Range, oTargetCell As Range
    Dim oWs As Worksheet
    
    Set oWs = Worksheets("bmaddition")
    Set oBottomCell = oWs.Cells(Cells.Rows.Count, 5).End(xlUp)
    Set oSearchRange = oWs.Range(oWs.Cells(1, 5), oWs.Cells(oBottomCell.Row, 5))
    With oSearchRange
        Set oTargetCell = .Find(silono.Value, SearchOrder:=xlByRows, _
        LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlPrevious)
        If Not oTargetCell Is Nothing Then
            With oTargetCell
                .Offset(, 1) = Me.silono.Value
                .Offset(, 2) = Me.silovol.Value
                .Offset(, 3) = Me.prodtype.Value
                .Offset(, 4) = Me.mono.Value
                .Offset(, 5) = Me.powdertype.Value
                .Offset(, 6) = Me.emptydate.Value
            End With
        End If
    End With
    Me.silono.Value = ""
    Me.silovol.Value = ""
    Me.prodtype.Value = ""
    Me.mono.Value = ""
    Me.powdertype.Value = ""
    Me.emptydate.Value = ""

End Sub


Regards.
 

Forum statistics

Threads
1,141,720
Messages
5,708,090
Members
421,546
Latest member
delatollas

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
Top