Matching rows/values with rows/values of another spreadsheet

[kwik10z]

Hi experts,

I have had great help from here, so i am coming back for more.

I need a macro that scans the attachment:
7250

and when it matches a row starting with "25" have it scan a second spreadsheet and match the "Reference Designator" Column F value.

I already have code that scans for the value of 25:

Public Sub PnP_Column1_FindSemiColon()
Dim rng As Range
Dim LastRow As Long
Dim oWB As Workbook
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set oWB = Workbooks.Add
Set rng = .Range("A1").Resize(LastRow)
rng.AutoFilter field:=1, Criteria1:="=;*", Operator:=xlOr, Criteria2:=25
rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy oWB.Worksheets(1).Range("A1")
oWB.Worksheets(1).Columns.AutoFit
rng.AutoFilter
Set rng = Nothing
End With
End Sub

If and when its a match, have it recognize and match that value to another value in a 3rd spreadsheet.

Sorry so confusing, but basically i need a macro to take the attached, scan column 1 for the value of "25" and then if the value is "25", use the value in Column F(Reference Designator)

should read something like U28 for example

and take it to farthest right values(Right function?) in column 2 of the second spreadsheet with should consisit of something like this:

SOG.090/4/LXK2/0.5W_U28 for example

there is more that needs to be done, but i wanna do this step by step. If my request is understandable--i will be amazed--and if someone can help out, thank you soo much.

you can reply to this with questions, or feel free to PM me. Good luck and i appreciate anyone who at least tries to help out.

[Aussiebear]

G'day kwik10z,

As I understand your post, you are needing code to find a trigger value of "25" within column a of the first sheet, then take the value of the cell 5 columns over from the trigger value and match it to a value on a second sheet. Then copy this matched value and add it to a 3rd sheet, with the reference designator added to the copied value as the right most section of the string. Is this right?

Could you post a workbook showing what the data on sheets 2 & 3 would look like, if this code you are chasing worked?

[kwik10z]

G'day kwik10z,

As I understand your post, you are needing code to find a trigger value of "25" within column a of the first sheet, then take the value of the cell 5 columns over from the trigger value and match it to a value on a second sheet. Then copy this matched value and add it to a 3rd sheet, with the reference designator added to the copied value as the right most section of the string. Is this right?

Could you post a workbook showing what the data on sheets 2 & 3 would look like, if this code you are chasing worked?

Pretty darn close. Here it is again with the attachments of workbooks.

Take [atachment in very first post] and create the trigger value of "25" within column A.

Then take the value of the cell 5 columns over (F) from the trigger value and match it to a value on 7321 Column A

If and when its a match (I.E. - J1) take the value of Column C and match it to [I will make a new post below with this attachment -- named FootPrint] column B (I.E. - SOG.090/4/LXK2/0.5W_J1)

*NOTE* the last step (^) the J1 is what i need matched to the right most format of the string, hence the column B example (SOG.090/4/LXK2/0.5W_J1)

There is more to the equation, as i have to take values of the second attachment and place them somewhere in the third attachment, but lets focus one step at a time! and if someone understands what i need from this jumbled mess, then maybe we cY an talk about what i need help with next! ( There are X,Y coordinates on the second attachment that have to be taken when the rows match, and input into a document. That is the next step basically after/with this code -- not sure exactly how i want to do it yet, but if we need to, we can get that info)

Thanks again to anyone who tries to assist me.

[kwik10z]

bumping it up!

[figment]

your ether missing a workbook in your links, or you have called out the wrong column names. but here is some code to get you started.


Sub testing()
Dim wsheet1 As Worksheet, wsheet2 As Worksheet
Dim row1 As Long, row2 As Long
Dim crit1 As String, crit2 As String
row1 = 9
Set wsheet1 = Workbooks("Macro Test 2 (FindNo25).xls").Worksheets("Sheet1")
Set wsheet2 = Workbooks("2x2component.xls").Worksheets("2x2component")
crit1 = 25
While wsheet1.Range("B" & row1) <> ""
If wsheet1.Range("A" & row1) = crit1 Then
row2 = 3
crit2 = wsheet1.Range("F" & row1)
While wsheet2.Range("B" & row2) <> ""
If wsheet2.Range("B" & row2) = crit2 Then
'last step
End If
row2 = row2 + 1
Wend
End If
row1 = row1 + 1
Wend
End Sub

this code makes the assumption that both workbooks are open.

[kwik10z]

your ether missing a workbook in your links, or you have called out the wrong column names. but here is some code to get you started.

what do you mean? where/how did you notice that, so i can go back and fix it accordingly!


Sub testing()
Dim wsheet1 As Worksheet, wsheet2 As Worksheet
Dim row1 As Long, row2 As Long
Dim crit1 As String, crit2 As String
row1 = 9
Set wsheet1 = Workbooks("Macro Test 2 (FindNo25).xls").Worksheets("Sheet1")
Set wsheet2 = Workbooks("2x2component.xls").Worksheets("2x2component")
crit1 = 25
While wsheet1.Range("B" & row1) <> ""
If wsheet1.Range("A" & row1) = crit1 Then
row2 = 3
crit2 = wsheet1.Range("F" & row1)
While wsheet2.Range("B" & row2) <> ""
If wsheet2.Range("B" & row2) = crit2 Then
'last step
End If
row2 = row2 + 1
Wend
End If
row1 = row1 + 1
Wend
End Sub

this code makes the assumtion that both workbooks are open.

what does this code do exactly so i know where to put it in my entire line of code that i have so far?

Thanks for your help

[figment]

if you go back and click on your linked workbooks, one of them doesn't load, i am not sure if this is a missing workbook or if you are referring to the one previously posted.

sorry about the lack of comments in the code, this one of my bad habits.

wsheet1 and wsheet2 both point to the worksheets where you are looking for info.

row 1 tracks what row you are on in the first worksheet

row2 track what row you are on in the second
crit1 tracks what your looking for in the first worksheet

crit2 tracks what you are looking for in the second.

i hardcoded most of the variables, for i was not sure if you had reliable markers to find them in software. and i am not sure what you are trying to do once you match the row in the first workbook to the row in the second. this may be because you seem to be lacking a third workbook, or because the columns in your description don't match the columns in your files. Either way i didn't write any code for after you match up the two worksheets.

[kwik10z]

This is the third workbook to be used.
7312

[kwik10z]

if you go back and click on your linked workbooks, one of them dosn't load, i am not sure if this is a missing workbook or if you are refering to the one previously posted.

Ya, i see what you mean. I forgot you could only attach one file! so i fixed that with corrections in blue font.

sorry about the lack of coments in the code, thst one of my bad habits.

Its cool

wsheet1 and wsheet2 both point to the worksheets where you are looking for info.

row 1 tracks what row you are on in the first worksheet

row2 treack what row you are on in the second

crit1 tracks what your looking for in the first worksheet

crit2 tracks what you are looking for in the second.

i hardcoded most of the varibles, for i was not sure if you had relyable markers to find them in software. and i am not sure what you are trying to do once you match the row in the first workbook to the row in the second. this may be because you seem to be lacking a third workbook, or because the columns in your discription dont match the columns in your files. ither way i didn't right any code for after you match up the two worksheets.
Thanks ill try it and play with it

[kwik10z]

Thanks for the code, when i talk to my boss, i will find out what i need to do exactly, and i will get back with you, hopefully today!! Because i just have to take some of the values and place them somewhere (i will find out exact location)

and i fixed the workbooks, so they all open now! sorry that they are all spread out in different post, but i keep forgetting there is a maximum of 1 file attachemnt per post.

[kwik10z]

is it possible to use some of the values on the spreadsheets and insert them into some triginometry functions in VBA?

[figment]

should be, but unfortunetly i dont know the function calls.