| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA searching through range and pasting value if no match is found
Hi im trying to complete my function, but i struggle! please help:
Public Function Generate_GetNum() Dim celle As String Dim hold As String Dim frompage As String Dim topage As String Dim i As Integer frompage = "Generert dokumentliste" topage = "Produktliste" For i = 2 To 20000 ' Aktiverer celle celle = Worksheets(frompage).Cells(i, 2) If celle = "" Then Exit For Else celle = Worksheets(frompage).Cells(i, 2) hold = Mid(celle, 10, 5) Worksheets(topage).Cells(i + 2, 1) = hold '<-- should be replaced ' Now it just pastes and does no search. here i want to search for value "hold" in Worksheets(topage).Cells(4, 1) and search stops down where first cell with no value. ' if value is not found in the search it should be pastet in the first empty cell who was found. ' if value is found then "next i" End If Next i End Function |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA searching through range and pasting value if no match is found
Hi
Try this: Public Function Generate_GetNum() Dim Celle As String Dim Hold As String Dim FromPage As String Dim ToPage As String Dim i As Integer FromPage = "Generert dokumentliste" ToPage = "Produktliste" For i = 2 To 20000 ' Aktiverer celle Celle = Worksheets(FromPage).Cells(i, 2).Value If Celle = "" Then Exit For Else 'celle = Worksheets(frompage).Cells(i, 2) Variable has already the cell value Hold = Mid(Celle, 10, 5) With Worksheets(ToPage) Set f = .Columns("A").Find(what:=Hold, lookat:=xlWhole) ' Search Column A for "Hold" If f Is Nothing Then .Cells(4, 1).End(xlDown).Offset(1, 0) = Hold End If End With End If Next i End Function Regards, Per "struggler" skrev i meddelelsen ... Hi im trying to complete my function, but i struggle! please help: Public Function Generate_GetNum() Dim celle As String Dim hold As String Dim frompage As String Dim topage As String Dim i As Integer frompage = "Generert dokumentliste" topage = "Produktliste" For i = 2 To 20000 ' Aktiverer celle celle = Worksheets(frompage).Cells(i, 2) If celle = "" Then Exit For Else celle = Worksheets(frompage).Cells(i, 2) hold = Mid(celle, 10, 5) Worksheets(topage).Cells(i + 2, 1) = hold '<-- should be replaced ' Now it just pastes and does no search. here i want to search for value "hold" in Worksheets(topage).Cells(4, 1) and search stops down where first cell with no value. ' if value is not found in the search it should be pastet in the first empty cell who was found. ' if value is found then "next i" End If Next i End Function |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA searching through range and pasting value if no match is f
there seems to be a problem, when it finds the "Hold" value then its ok, but
if it does not find the value there is an error: If f Is Nothing Then .Cells(4, 1).End(xlDown).Offset(1, 0) = Hold End If Im getting a run-time error. Application-defined or object.defined error. Any suggestions? "Per Jessen" wrote: Hi Try this: Public Function Generate_GetNum() Dim Celle As String Dim Hold As String Dim FromPage As String Dim ToPage As String Dim i As Integer FromPage = "Generert dokumentliste" ToPage = "Produktliste" For i = 2 To 20000 ' Aktiverer celle Celle = Worksheets(FromPage).Cells(i, 2).Value If Celle = "" Then Exit For Else 'celle = Worksheets(frompage).Cells(i, 2) Variable has already the cell value Hold = Mid(Celle, 10, 5) With Worksheets(ToPage) Set f = .Columns("A").Find(what:=Hold, lookat:=xlWhole) ' Search Column A for "Hold" If f Is Nothing Then .Cells(4, 1).End(xlDown).Offset(1, 0) = Hold End If End With End If Next i End Function Regards, Per "struggler" skrev i meddelelsen ... Hi im trying to complete my function, but i struggle! please help: Public Function Generate_GetNum() Dim celle As String Dim hold As String Dim frompage As String Dim topage As String Dim i As Integer frompage = "Generert dokumentliste" topage = "Produktliste" For i = 2 To 20000 ' Aktiverer celle celle = Worksheets(frompage).Cells(i, 2) If celle = "" Then Exit For Else celle = Worksheets(frompage).Cells(i, 2) hold = Mid(celle, 10, 5) Worksheets(topage).Cells(i + 2, 1) = hold '<-- should be replaced ' Now it just pastes and does no search. here i want to search for value "hold" in Worksheets(topage).Cells(4, 1) and search stops down where first cell with no value. ' if value is not found in the search it should be pastet in the first empty cell who was found. ' if value is found then "next i" End If Next i End Function |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA searching through range and pasting value if no match is f
Hi again.
I solved the problem, maby not the best way bit still, it works: If f Is Nothing Then .Cells(4, 1).End(xlDown).Activate ActiveCell.End(xlDown).Activate ActiveCell.End(xlUp).Activate ActiveCell.Offset(1, 0) = Hold End If Thanks for your help Per Jessen!!! "struggler" wrote: there seems to be a problem, when it finds the "Hold" value then its ok, but if it does not find the value there is an error: If f Is Nothing Then .Cells(4, 1).End(xlDown).Offset(1, 0) = Hold End If Im getting a run-time error. Application-defined or object.defined error. Any suggestions? "Per Jessen" wrote: Hi Try this: Public Function Generate_GetNum() Dim Celle As String Dim Hold As String Dim FromPage As String Dim ToPage As String Dim i As Integer FromPage = "Generert dokumentliste" ToPage = "Produktliste" For i = 2 To 20000 ' Aktiverer celle Celle = Worksheets(FromPage).Cells(i, 2).Value If Celle = "" Then Exit For Else 'celle = Worksheets(frompage).Cells(i, 2) Variable has already the cell value Hold = Mid(Celle, 10, 5) With Worksheets(ToPage) Set f = .Columns("A").Find(what:=Hold, lookat:=xlWhole) ' Search Column A for "Hold" If f Is Nothing Then .Cells(4, 1).End(xlDown).Offset(1, 0) = Hold End If End With End If Next i End Function Regards, Per "struggler" skrev i meddelelsen ... Hi im trying to complete my function, but i struggle! please help: Public Function Generate_GetNum() Dim celle As String Dim hold As String Dim frompage As String Dim topage As String Dim i As Integer frompage = "Generert dokumentliste" topage = "Produktliste" For i = 2 To 20000 ' Aktiverer celle celle = Worksheets(frompage).Cells(i, 2) If celle = "" Then Exit For Else celle = Worksheets(frompage).Cells(i, 2) hold = Mid(celle, 10, 5) Worksheets(topage).Cells(i + 2, 1) = hold '<-- should be replaced ' Now it just pastes and does no search. here i want to search for value "hold" in Worksheets(topage).Cells(4, 1) and search stops down where first cell with no value. ' if value is not found in the search it should be pastet in the first empty cell who was found. ' if value is found then "next i" End If Next i End Function |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA searching through range and pasting value if no match is f
Hi
Thanks for your reply, I'm glad that you made it work. Regards, Per On 27 Nov., 16:13, struggler wrote: Hi again. I solved the problem, maby not the best way bit still, it works: *If f Is Nothing Then * * * * * * * * * * .Cells(4, 1).End(xlDown).Activate * * * * * * * * * * ActiveCell.End(xlDown).Activate * * * * * * * * * * ActiveCell.End(xlUp).Activate * * * * * * * * * * ActiveCell.Offset(1, 0) = Hold * * * * * * * * End If Thanks for your helpPerJessen!!! "struggler" wrote: there seems to be a problem, when it finds the "Hold" value then its ok, but if it does not find the value there is an error: *If f Is Nothing Then * * * * * * * * * * .Cells(4, 1).End(xlDown).Offset(1, 0) = Hold * * * * * * * * End If Im getting a run-time error. Application-defined or object.defined error. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to match 2 columns, if a match found add info from 2nd column | Excel Worksheet Functions | |||
| Searching for a value for and then replacing when found. | Excel Programming | |||
| Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
| Conditional format if cell match found in another range of cells | Excel Worksheet Functions | |||
| Searching for partial text match in range | Excel Worksheet Functions | |||
Linear Mode
