find value in one sheet use this as the row to copy the value in c

[Guest]

Hello I have 2 sheets called planning and printing.

I would like to use the value in cell a3 of worksheet printing to search
range e3:e61 in worksheet planning for the same value, (this gets the right
row but I need the value in column A) and with this value in column A, copy
it into worksheet printing cell D3.

Id like to repeat this process going down column a of worksheet printing,
still searching e3:e61 in worksheet planning, copy value in column A and
place it in column D of worksheet printing.

is this possible?

 

[Don Guillett]

Have a look in vba help for FINDNEXT. >use something like
dlr=sheet("sheet1").cells(rows.count,"a").end(xlup).row+1 to find the next
avail row to copy to.

 

[Guest]

I ve had a look and I am so confused it is like another language to me. I
must admit even your example is goobledy goop to me. can you suggest anything
else.

cheers

 

[Don Guillett]

A re-read suggests that you are only doing this once so a formula approach
would be better.
Look in excel help for MATCH function to find the row desired. Then look at
INDEX to see how that works and incorporate the match formula within the
index formula.

 

[Guest]

Dear Don I am not doing this once but several times, with 30 columns.

I have this code but it falls down when there is no match

Public Sub test()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
Worksheets("printing").Activate
Set myrange = Range(Range("a3"), Cells(Rows.Count, 1).End(xlUp))
For Each cell In myrange
Worksheets("planning").Activate
With Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)
Set x = cfind.End(xlToLeft)
End With
Worksheets("printing").Activate
cell.Offset(0, 3) = x
Next cell
End Sub

 

[Don Guillett]

Have a try at using

onerror resume next
On Error Statement


Enables an error-handling routine and specifies the location of the routine
within a procedure; can also be used to disable an error-handling routine.

Syntax

On Error GoTo line

On Error Resume Next

On Error GoTo 0

The On Error statement syntax can have any of the following forms:

Statement Description
On Error GoTo line Enables the error-handling routine that starts at
line specified in the required line argument. The line argument is any line
label or line number. If a run-time error occurs, control branches to line,
making the error handler active. The specified line must be in the same
procedure as the On Error statement; otherwise, a compile-time error occurs.
On Error Resume Next Specifies that when a run-time error occurs,
control goes to the statement immediately following the statement where the
error occurred where execution continues. Use this form rather than On Error
GoTo when accessing objects.
On Error GoTo 0 Disables any enabled error handler in the current
procedure.

 

[Don Guillett]

also something like this may (NOT tested) be better to remove selections.
NEEDS testing.

Sub test()
Dim cell As Range
Dim myrange As Range

with Worksheets("printing")
Set myrange = .Range(Range("a3"), .Cells(Rows.Count, 1).End(xlUp))
For Each cell In myrange
Worksheets("planning").Range("e3:e61").Find(what:=cell.Value,
lookat:=xlWhole).End(xlToLeft) _
copy .cell.Offset(0, 3)
Next cell
end with
End Sub

 

[danetrak]

hi Paul

this is a simple one, i did a demo for you, but you address bounced
back, reply to me and i will sent to you

 

[Guest]

Hi danetrak!
thanks for your reply!
I have sent you an email.

my email address is (e-mail address removed)

 

[Tom Ogilvy]

You can just test that the find was successful:

Public Sub test()
Dim cfind As Range
Dim x As Range
Dim cell As Range
Dim myrange As Range
With Worksheets("printing")
Set myrange = .Range(.Range("a3"), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In myrange
With Worksheets("planning").Range("e3:e61")
Set cfind = .Cells.Find(what:=cell.Value, lookat:=xlWhole)
If not cFind is nothing then
Set x = .Cells(cfind.row,1)
Worksheets("printing").Activate
cell.Offset(0, 3).Value = x.Value
Else
' optional:
' cell.offst(0,3).Value = "not found"
end if
End With
Next cell
End Sub

 

[Guest]

Almost there just have to copy the formatting as well.

Also If the value in worksheet printing is blank, that is the value I am
trying to match in col A is blank I would like the formula to not search for
a value. I would like the result in col D to automatically be blank

thanks again

 

[Tom Ogilvy]

If your working privately with danetrack, don't post in the newgroup please.

 

[Guest]

No I was not working privately.

I don't understand the question>