Microsoft Office Forums Using VBA to return values from several cells as a formula.
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-23-2023, 11:56 AM
 BYahr Windows 11 Office 2021 Novice Join Date: Mar 2023 Posts: 16
Using VBA to return values from several cells as a formula.

I'm trying to get the data from more than one cell to combine in another cell.

Example: In worksheet 1, cells A23, A24 and A25 have dollar values.
In cell Q165 of worksheet 2, the desired result is =value of A23 + value of A24 - value of A25. (or =35+100-60). Showing in Q165 is 75.
#2
05-24-2023, 04:03 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 793

I don't know what your sheets' names are so you'll need to adjust this:
Code:
```With Sheets("Sheet1")
Sheets("Sheet2").Range("Q165").Value = .Range("A23").Value + .Range("A24").Value - .Range("A25").Value
End With```
…and there are many other ways.
#3
05-24-2023, 04:53 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 817

Why don't you simply use a formula in target sheet (Sheet2!Q165)
Code:
`=SUM(Sheet1A23:A25)`
#4
05-25-2023, 11:58 AM
 BYahr Windows 11 Office 2021 Novice Join Date: Mar 2023 Posts: 16

Thank you, p45cal. This helps. The result is the correct value but I would prefer to get the individual values in a formula. EX =100+5-1 to display 104. This makes it easy to know where the values originated.

The sheets that I am working with are in two different workbooks.
#5
05-31-2023, 12:00 PM
 kilroyscarnival Windows 10 Office 2021 Expert Join Date: May 2019 Posts: 308

Oh, you want to actually see the individual figures?

=CONCATENATE("="&A23&"+"&A24&"+"&A25)

That cell will now display "=35+100+-60" but it's merely a text string.

If you wanted to make it a calculable number again, copy, paste values, and then you'll need to do something to refresh that cell for it to convert back to a number where it will display the "75" but in the formula bar you will see the =35+100+-60. You can click inside the formula then out and that should refresh it. My cheat involves having a macro button that runs a macro simply replacing every digit from 0 through 9 with itself, thereby refreshing all numbers.
#6
06-06-2023, 11:47 AM
 BYahr Windows 11 Office 2021 Novice Join Date: Mar 2023 Posts: 16

Thank you, Kilroyscarnival.

This helped. Still had to adapt to work between two workbooks, but the cell is working.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Oluagbe1 Excel 10 02-11-2019 07:00 PM trevorc Excel Programming 4 10-11-2018 03:49 PM Kubi Excel 3 08-07-2017 11:52 PM GiJoe81 Excel 1 06-27-2015 12:13 AM tinfanide Excel 4 08-30-2014 07:03 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:54 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top