Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-23-2023, 11:56 AM
BYahr BYahr is offline Using VBA to return values from several cells as a formula. Windows 11 Using VBA to return values from several cells as a formula. Office 2021
Novice
Using VBA to return values from several cells as a formula.
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 05-24-2023, 04:03 AM
p45cal's Avatar
p45cal p45cal is offline Using VBA to return values from several cells as a formula. Windows 10 Using VBA to return values from several cells as a formula. Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #3  
Old 05-24-2023, 04:53 AM
ArviLaanemets ArviLaanemets is offline Using VBA to return values from several cells as a formula. Windows 8 Using VBA to return values from several cells as a formula. Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Why don't you simply use a formula in target sheet (Sheet2!Q165)
Code:
=SUM(Sheet1A23:A25)
Reply With Quote
  #4  
Old 05-25-2023, 11:58 AM
BYahr BYahr is offline Using VBA to return values from several cells as a formula. Windows 11 Using VBA to return values from several cells as a formula. Office 2021
Novice
Using VBA to return values from several cells as a formula.
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 05-31-2023, 12:00 PM
kilroyscarnival kilroyscarnival is offline Using VBA to return values from several cells as a formula. Windows 10 Using VBA to return values from several cells as a formula. Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Oh, you want to actually see the individual figures?

In your destination cell try:

=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.
Reply With Quote
  #6  
Old 06-06-2023, 11:47 AM
BYahr BYahr is offline Using VBA to return values from several cells as a formula. Windows 11 Using VBA to return values from several cells as a formula. Office 2021
Novice
Using VBA to return values from several cells as a formula.
 
Join Date: Mar 2023
Posts: 16
BYahr is on a distinguished road
Default

Thank you, Kilroyscarnival.

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

Reply With Quote
Reply

Tags
combine data in cells, vba to display a formula.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to match one list with another and return certain values from corresponding cells in excel Oluagbe1 Excel 10 02-11-2019 07:00 PM
Using VBA to return values from several cells as a formula. Loop through cells and return only unique values trevorc Excel Programming 4 10-11-2018 03:49 PM
Using VBA to return values from several cells as a formula. have a formula that combines two cells return a zero instead of a blank cell Kubi Excel 3 08-07-2017 11:52 PM
Formula/function to return column names for matching values GiJoe81 Excel 1 06-27-2015 12:13 AM
Using VBA to return values from several cells as a formula. Excel Formula: return a range of cells that match tinfanide Excel 4 08-30-2014 07:03 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft