Question regarding difficult vba function

klovn1

New Member
Joined
Mar 30, 2016
Messages
3
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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) :

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top