Macro to copy and paste result of formula

dariushou

Board Regular
Joined
Feb 17, 2008
Messages
126
So i have data in cell A1 of sheet 1 that is in this format:

HTML:
41,477,729.68 (8.64%)

I currenlty use the following formula to pull the 8.64% out of the cell:

HTML:
=-right(a1,8)

This works fine, but i've created a macro that pulls all of my data instead of relying on links which always seem to get screwed up. Since in this instance, i need to copy the result of a formula instead of just raw data, i've become stuck. Any ideas?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
dariushou,

Can we see more of your data?

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Colo's HTML Maker.
http://www.puremis.net/excel/downloads.shtml

Instructions for using "Colo's HTML Maker":
http://www.mrexcel.com/forum/showthread.php?t=89356


Or, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/index.php?f=1

Instructions for using "Excel Jeanie HTML 4":
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Also, please post your macro code.

At the beginning of your posted code, enter the following without the quote marks:
["code"]


Your code goes here.


At the end of your posted code, enter the following without the quote marks: ["/code"]



Have a great day,
Stan
 
Last edited:
Upvote 0
Please don't laugh at my code--i'm very new to vba. Here's the code:

Code:
Sub Test11()
'01B
    Workbooks.Open Filename:="C:\Darius\01B.xls"
    Worksheets("01B Collat").Range("F4:F364").Copy
    Workbooks.Open Filename:="C:\\Darius\resids 5.31.08.xls"
    Workbooks("resids 5.31.08.xls").Worksheets("01B").Range("A31").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
 
    Workbooks("01B.xls").Worksheets("01B Collat").Range("n4:o364").Copy
    Workbooks("resids 5.31.08.xls").Worksheets("01B").Range("B31").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
 
    Workbooks("01B.xls").Worksheets("01B XIO").Range("E4:E364").Copy
    Workbooks("resids 5.31.08.xls").Worksheets("01B").Range("D31").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Workbooks("01B.xls").Close
 
End Sub

Basically, the code goes through each worksheet of the workbook "01B.xls" and grabs data from certain columns and pastes that data into a worksheet called "01B" in the workbook "resids 5.31.08.xls". I have to do this for 28 files that each have there own worksheet in the "resids 5.31.08.xls" workbook.

I'm trying to insert a section in the above code that will look at the worksheet "01B PY" in the "01B.xls" workbook and perform the calculation =-right(Cell C10,8) and then copy the result and paste it into Cell N16 in the worksheet "01B" in the workbook "resids 5.31.08.xls".

The data in C10 looks like this 41,989,425.72 (6.00%). The formula =-right(C10,8) works perfect outside of vba.


However, i'm not sure if you can use this formula in vba. I heard something about a LEN formula. Then i wouldn't know how to copy the result of a calculation that you do in vba either.

Thanks for your help.

Darius
 
Last edited:
Upvote 0
Anyone? I'm not sure how to perform the correct calculation in vba and then copy the result of that calculation into a workbook.
 
Upvote 0
dariushou,

Sorry for the delay - have been consumed with the building of our new home.

Hope this helps:

Excel Workbook
ABCD
141,477,729.68 (8.64%)8.64%
2
3
4
5
6
7
8
9
1041,989,425.72 (6.00%)6.00%
Sheet1



The formulae in cell B1, or D10, from VBA:
Code:
    Range("B1").FormulaR1C1 = "=-RIGHT(RC[-1],LEN(RC[-1])-FIND("" "",RC[-1],1))"

    Range("D10").FormulaR1C1 = "=-RIGHT(RC[-1],LEN(RC[-1])-FIND("" "",RC[-1],1))"


Have a great day,
Stan
 
Upvote 0
Thanks for the reply Stan! The issue is that i don't have the formula in cell B1. I get this output from a program that exports to excel in this format. I have this macro in another workbook(lets say, "YT") that goes into the excel export workbook and grabs data from the sheet and one of the items that i need is the result of your equation 8.64%. So in vba i would need to perform the calculation and then paste the result of that calculation into a work book "Summary". I just don't know how to do all of this in vba because i don't have the result B1 as in your example.
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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