PDA

View Full Version : Match and statements in sheets



elmnas
07-30-2015, 06:58 AM
Hello all VB Members,

I should need help with a code,
The thing is I have got a workbook that contains two sheets “Statistics” and “Prices”
and I need a function that check for certain values.
For each Row in Sheet “Statistics” Check the values in each row for Column H and I and J
Example:
H1:SWE
I1:GER
J1:25
Picture(Sheet Statistics)

14037

(all these 3 values are on same row (Row1))

For Each Row in Sheet “Prices” Check for the values but in this case instead SWE=(Column A) and Ger (Column B)
When found multiple the value 25 (Column J1, Sheet “Statistics”) * G1:3 (Sheet”Prices”)
Represent the result in Sheet “Statistics” same row but Column “N1”
Picture (Sheet Prices)

14038

The thing is…
As the example I showed you (H1:SWE) and (i1:GER)
those values can be anything as so long they match the same values in sheet “prices”
Could someone help me ?
Thank you in advance

p45cal
07-30-2015, 11:25 AM
What's G1:3 (Sheet”Prices”)?

A workbook might be useful. Link to one in the cloud or attach one here?

Also, remember this?!!:http://www.vbaexpress.com/forum/showthread.php?52722-Need-help-to-autofil-with-VBA&p=325323&viewfull=1#post325323

elmnas
07-31-2015, 12:34 AM
What's G1:3 (Sheet”Prices”)?

A workbook might be useful. Link to one in the cloud or attach one here?

Also, remember this?!!:http://www.vbaexpress.com/forum/showthread.php?52722-Need-help-to-autofil-with-VBA&p=325323&viewfull=1#post325323

I remember the the thread I posted earlier the thing is there are two statements that need to much and I have no clue how to make 2 to a pair and then match them with two more.

G1:3 (sheet "Prices)
sorry for a bad explanation, I meant of course.
in J1 there is value 25 then multiply the value with G1=3 (G1 have this example value 3)
then represent the new value in Statistics in same row but Column N1

p45cal
07-31-2015, 01:17 AM
G1 does not = 3, it contains 'No Match'
G3 however contains 0.075, is this what you're referring to?
But row 3 doesn't have SWE and GER, so I'm totally confused.

Again, a workbook might be useful. Link to one in the cloud or attach one here?

When I asked 'remember this?' I was pointing to a specific post, not the whole thread. Read http://www.excelguru.ca/content.php?184 . Supply links to your crossposts. I will not take this further until you do.

elmnas
07-31-2015, 01:25 AM
Sorry if I haven't explained it well but..

I have got two sheets
sheet1 and sheet2
sheet1 contains following columns:
"H" and "I"
and sheet2 contains following columns:
"A" and "B"
I need a function that loops each row and the whole sheet,
example:
Sheet 1 Column "H" have value 123 (example value could be any)
Sheet1 Column "I" have value 567 (example value could be any)


I need to check in sheet2
if Column"A" have value 123 also column "B" have value 567
then msgbox the equals cells in sheet2

I have made this code before but no one of them work

elmnas
07-31-2015, 02:28 AM
I have made this code so far but I dont want to alert empty cells

Sub Compare()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim r As Long
Dim m As Long
Dim s As Long
Dim n As Long


Set ws1 = ActiveSheet
m = ws1.range("A" & ws1.Rows.Count).End(xlUp).Row

Set ws2 = Worksheets("sheet2")
n = ws2.range("A" & ws2.Rows.Count).End(xlUp).Row

For r = 1 To m
For s = 1 To n

If ws1.range("A" & r) = ws2.range("A" & s) And _
ws2.range("B" & r) = ws2.range("B" & s) Then


MsgBox ws2.range("A" & s).Text
Exit For
End If
Next s
Next r
End Sub