need help in formula for multiply cell with another cell in vba

[star64]

I need help in coding the problem below in vba:

I have two groups of cells,

For the first group, I have cells(1,1) = 1, cells(1.,2) = 2, cells(1,3) = 3, and cells(1,4) = 4.

For the second group, cells(1,5) = 44, cells(1,6) = 13, cells(1,7) = 14, cells(1,8) = 23, and cells(1,9) = 34

I want to make the formula for the cells in second group = the multiplication of cells from the first group together. By choosing the cells that have matching value.

For example, cells(1,6).formula ="=cells(1,1)*cells(1,3)"

This is because cells(1,6) = 13 -----> cells(1,1) = 1 and cells(1,3) = 3

When I did try "=cells(1,1)*cells(1,3)" or somethingelse it gave me #NAME? in the cell

Could you please guide me the syntax for coding this problem?

Thank you very much

 

[pgc01]

Hi

Try this code:

Sub Test()
Dim r1 As Range, r2 As Range, rC As Range
 
Cells(1, 1) = 1: Cells(1, 2) = 2: Cells(1, 3) = 3: Cells(1, 4) = 4
Cells(1, 5) = 44: Cells(1, 6) = 13: Cells(1, 7) = 14: Cells(1, 8) = 23: Cells(1, 9) = 34
 
Set r1 = Range(Cells(1, 1), Cells(1, 4))
Set r2 = Range(Cells(1, 5), Cells(1, 9))
 
For Each rC In r2
    rC.Formula = "=" & r1(Left(rC, 1)).Address & "*" & r1(Right(rC, 1)).Address
Next rC
End Sub

 

[star64]

Dear pgc01,

Thank you very much for your help.

I am sorry for the late reply. I was trying to apply your code into my work.
I still got a few questions for you.

My work is what you see on the spreadsheet. I want to put the formula on the blue area.

Below is the code:
-------------------------------------------------------------------------
Sub Test1()
Dim r1 As Range, r2 As Range, r3 As Range, rC As Range
Dim f As Integer
Dim i As Integer

n = 9

f = 0
For i = 1 To n
If Cells(4, 2) = 0 Then
f = f + 1
Else
End If
Next i

Set r1 = Range(Cells(4, 1), Cells(3 + f, 1))
Set r2 = Range(Cells(4 + f, 1), Cells(3 + n, 1))
Set r3 = Range(Cells(4 + f, 2), Cells(3 + n, 2))

For i = 1 To n - f

Cells(3 + f + i, 7).Formula = "=" & r1(r2, 1).Address & "*" & r1(r3, 1).Address

Next i

End Sub

-------------------------------------------------------------------------
Below are questions:

1. Now the number of r2 (your code) is divided into r2 and r3 (not left and right as before). I did try to adjust your code, but it does not work because I don't know how to use For Each ... In ...

Could you please help me out correcting the code?

2. I want to check If r1 include all the numbers appear in r2 and r3 before the multiplication. If it is not, I want to add the cell for that number into r1 before the multiplication.

For example, If r1 contains 1, 2, 4 (not 3), I want to add the row for number 3, then do the multiplication as normal.

I could not figure out what the code should be. Could you please help me out on this one?

Thank you very much for your help. I really appreciate it.

 

[pgc01]

Hi

FOr question 1, try this code. I tried to change your code as little as possible.

Sub Test1()
Dim r1 As Range, r2 As Range, r3 As Range
Dim f As Long
Dim i As Long
Dim n As Long
 
n = 9
f = 0
For i = 0 To n - 1
    If Cells(4 + i, 2) <> 0 Then Exit For
    f = f + 1
Next i
 
Set r1 = Range(Cells(4, 1), Cells(3 + f, 1))
Set r2 = Range(Cells(4 + f, 1), Cells(3 + n, 1))
Set r3 = Range(Cells(4 + f, 2), Cells(3 + n, 2))
 
For i = 1 To n - f
    Cells(3 + f + i, 7).Formula = "=" & r1(r2(i).Value).Address & "*" & r1(r3(i).Value).Address
Next i
End Sub

I don't understand question 2.

HTH

 

[star64]

Dear pgc01,

Thank you very much for the code on question1.
I tried the code. It works well.

For my question2,

I mean I want to check if my r1 contains all number need in r2 and r3.

If r1 does not the number need in r2 or r3, I want to add the row of that number into r1

For example (Spreadsheet below), r1 does not contain number "3" which need in calculation of r2 and/or r3

Therefore, I want to add the row for number "3" into r1 (row 6) as shown in spreadsheet below

Again, thank you very much for your time. I really appreciate it.