Question regarding difficult vba function

[klovn1]

Hi.

I am pretty new to VBA, and have been struggling with a function for a while. I am trying to add two cells from two different sheets, and place the subract in a specific cells on a sheet. I am doing this about 600 times, so I probably need a loop. I know how to do it if only pasting the result in number, but what make this difficult is how I can have the result in formula like (='R'!E38+Sheet10!C34) and not just like (-0.0098292834). I am using loop so the placing of result and the summation cell must also be changing.


THanks in advance for all the help.

Kind Regards

 

[GTO]

Hi there and welcome to the forum

Can you describe in plain English what cells/ranges we are looping through and where the results should be?

Mark

 

[klovn1]

Hi.

Thanks for fast response. I am not sure how to describe it good, but lets say I have two sheets;Sheet1 and sheet2. I have a bunch of number in sheet1 and one number in sheet2(A1). I am trying to subtract one cell from each column in sheet1 with sheet2(A1). What I have so far is

Sub test3()

For i = 1 To 400

s = Sheets("remove").Cells(i + 1, 4).Value

Cell(s, i).Formula = sheets("Sheet1").Cells(s, i) -sheets("sheet2).Cells(3, 2)

Next i

End Sub



But that only input the exact number and not the formula. And also I need the result to be pasted on specific places. The above is just for illustrative purpose.

 

[GTO]

I am not sure, but if I understand, you are wanting VBA to write the formula in various cells, is that correct?

If yes, then what sheet does the unqualified cells() belong to?

Cell(s, i).Formula = sheets("Sheet1").Cells(s, i) -sheets("sheet2).Cells(3, 2)

 

[klovn1]

the red marked cells is referring to sheet2.

 

[GTO]

Okay, I am still not "seeing" your data layout in my poor head, but in a junk copy of your workbook, try (in a Standard Module) :

Option Explicit
  
Sub test3()
Dim s As Double, i As Long

For i = 1 To 400
  
  s = ThisWorkbook.Worksheets("remove").Cells(i + 1, 4).Value
  
  ThisWorkbook.Worksheets("Sheet2").Cells(s, i).Formula = _
    "=" & Mid$(ThisWorkbook.Worksheets("Sheet1").Cells(s, i).Address(True, True, xlA1, True), InStrRev(ThisWorkbook.Worksheets("Sheet1").Cells(s, i).Address(True, True, xlA1, True), "]") + 1) & _
    "-" & Mid$(ThisWorkbook.Worksheets("Sheet2").Cells(3, 2).Address(True, True, xlA1, True), InStrRev(ThisWorkbook.Worksheets("Sheet2").Cells(3, 2).Address(True, True, xlA1, True), "]") + 1)
Next i

End Sub

Does that end up with the formulas as expected?

Mark