Matching Data - Loop

[k21k]

Hi

What I am trying to do is pretty simple (this is what I imagine)

I have a 4 columns
- the columns 1 and 2 contain a number of days and an associated number
- the columns 3 and 4 contain a number of days and an associated number

My formula should do this:
- if the number of days (col 1 and 3) are the same, then multiply the number in col 3 and 4 together.
- more specifically, for each number in col 1, the formula looks all matching values in col 3 and makes a simple calculation
- result displayed in column 5

Constraints
- col 1 has more data than col 3
- the data in both column are not sorted

What I have so far:

Dim x As Integer

For x = 1 To 10 Step 1
If Cells(x, 1) = Cells(x, 3).Value Then
Cells(x, 5).Value = Cells(x, 3) * Cells(x, 4)
End If
Next x

Could someone help me on this?
Thanks a lot in advance

[mudraker]

Welcome to Exceltip forum

Please take a couple of minutes and read the Forum Rules then wrap your VBA code (Rule 3)

[rwgrietveld]

Why not just use:

E1=IF(A1=C1;C1*D1;0)

Gr,
Ricardo

[davesexcel]

Wrap code like this, click the # icon and paste the code

Please Login or Register  to view this content.

[k21k]

Hi Ricardo

Actually, I want to write the formula because this example will be used to process a more complex task (launching a solver instead a doing a multiplication).

do you have an idea about the VBA?

Thanks a lot for your time

[k21k]

Hi Dave

Your macro works, but applies the calculation only 1 time.
Is it possible to make it work for all values in col 1 successively?

Thanks in advance

[Cheeky Charlie]

Could you post a sample?

I think you need to loop through your values in A and lookup values in C to find multiplicates - but I don't want to take time to build this because I'm not exactly sure what you need...

[k21k]

here is the file

[mudraker]

k21k

I asked you to read forum rules & wrap your VBA code

following a moderators request is also listed listed in the rules

I am closing this thread until you PM me that you have read the rules & will follow the rules


Edit
Thread re-opened as I have received a PM from k21k

[k21k]

I have wrapped my code. Sorry for my mistake, will not happen again.
Thanks for having reopened my thread.

Is anyone able to help on this topic?
Please have a look at the attached spreadsheet.
The main issue is that the #days in the col 1&2 are matching, but not on the same rows.

Thanks a lot in advance

[k21k]

Could anyone help on this one?

[Cheeky Charlie]

This seems pretty straightforward but it's still not clear what you want:
For day1 - 3, value 15, there are two values in Day2, values 12 and 24, - do you want:
15*12*24
(15*12) + (15*24)
15*12, 15*24 adjacent
?

[davesexcel]

Could anyone help on this one?

Hi Give this a go,

Please Login or Register  to view this content.