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

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

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

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

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.