# Convert Formula To Macro Code (combining Data From Two Cells)

Jul 28, 2009
I received assistance from NBVC for combining data from two cells. Post http://www.excelforum.com/showthread.php?p=2135144. I need to convert the formula into macro code. I thought I would be able to convert it on my own, but running into some troubles with run time errors. Here is the Excel formula, which is working fine.

View 5 Replies
ADVERTISEMENT
Nov 24, 2006

I have

1) A worksheet ( named PC OD) with one 80X80 matrix (matrix1)

2) another worksheet (named worksheet2) where I have 2 80X80 matrices (matrix 2 and matrix 3) and the below mentioned formulae

Matrix 2 grabs data off Matrix 1 (cell 'PC OD'!B769 belongs to matrix 1, in sheet PC OD) to round off the values in each of the 80X80 cells : ...

View 9 Replies
View Related
Apr 3, 2014

I have VBA code the extracts data from a source and puts it into excel. I need to change the format of some of the extracted data so i can use it for a VLookup formula. Reference the attached example. Columns A AND B have the extraced data which are dates. In Columns C and D or E and F I have typed what I would like the data to look like so I can use in a Vloolup formula. Is there VBA or a formula that word change the format of the dates in Columns A and B to look like C and D or E and F. The changed format could be moved to any new cells or rows.

View 1 Replies
View Related
Apr 18, 2008

I'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors

Worksheets("Output").Range("K14").Formula = "Worksheets("Calcs").Range("D17")*worksheets("Calcs").Range("D17").Offset(10+T,0)"

When I record a macro it gives me this, but again, I need the last cell in terms of "T"

ActiveCell.FormulaR1C1 = "=Calcs!R[3]C[-7]*Calcs!R[14]C[-7]"

View 3 Replies
View Related
Feb 17, 2014

Below is my requirement and i have to convert formulas into vba code:

If dt = "A0" Or dt = "A1" Then

Sheet1.Range("K").Formula = "$J/$ I" ' Formula is k=J/I

Sheet1.Range("K:K" & last_row).FillDown

Sheet1.Range("L").Value = "20.00%"

Sheet1.Range("L:L" & last_row).FillDown //Formula is L=20% for now i have just hardcoded the value there.

Sheet1.Range("M").Value = "=($K-$L)* 100" 'Formula is M=(K-L)/*100

Sheet1.Range("M:M" & last_row).FillDown

End If

View 5 Replies
View Related
Feb 25, 2014

How can I write this formula in vba code:

RevName = "Randolph Wakabayashi"

Range("A1").Value =LOWER((Left(Right(RevName, Len(RevName) - Find(" ", RevName, 1)), 5)) & Left(RevName, 2))

View 3 Replies
View Related
Dec 15, 2006

I have a step in a very long macro to populate a sheet (SheetMaster) with the course completion status based on another sheet (SourceData) in the same workbook.

On "SheetMaster" Col E starting with row 2, I want to look at whether a concatenated value of E1 + A2 is in Col G in "SourceData". The macro should run until it gets to a blank row in Col A.

Here is an example to help illustrate the logic:

E1 = Ethics101

A2 = 123456

A3 = 654321

If Col G in "SourceData" contains "Ethics101-123456", E2 = "Complete", if not "Incomplete"

If Col G in "SourceData" contains "Ethics101-654321", E3 = "Complete", if not "Incomplete"

...and so on until it gets to a blank row in Col A...

View 9 Replies
View Related
Mar 11, 2009

I'm manually entering a 10 digit code into Col A; at this moment in time I have the following formula in Col B:

View 14 Replies
View Related
Jul 4, 2012

(1) =round(if(bran!g1="raw",if(bran!n1=$f$19,(bran!p1/$g$19)*(bran!n1-$g$19),if(bran!n1>=$f$20,((bran!p1/$g$19)*($f$19-$g$19))+((bran!p1/$g$19)*(bran!n1-$f$19)*2),if(bran!n1=$j$20,((bran!p1/$k$19)*($j$19-$k$19))+((bran!p1/$k$19)*(bran!n1-$j$19)*2),if(bran!n1$f$25,bran!j1*$h$25,if(bran!o1>$f$26,(bran!o1-$f$26)*bran!j1*$h$26,if(bran!o1$j$25,bran!j1*$l$25,if(bran!o1>$j$26,((($j$26-$j$27)*$l$27*bran!j1)+((bran!o1-$j$26)*bran!j1*$l$26)),if(bran!o1>$j$27,(bran!o1-$j$27)*bran!j1*$l$27,if(bran!o1

View 1 Replies
View Related
Jul 8, 2005

I've been trying to modified the formula below into the code you see below.

= SUMIF(G3:G21,">0",G3:G21)-SUMIF(A3:A21,"=",G3:G21)

So how can to modified the SUMIF formula into a code to the finalrow?

Range("J" & FinalRow + 1).Formula = "=SUM(J2:J" & FinalRow & ")"

View 9 Replies
View Related
Apr 17, 2006

I have a spreadsheet with a name spread over 3 cells

A1 = MR

B1 = JOHN

C1 = SMITH

i want to create a macro or a formula that combines all of the data in these cells into the first column so that in column A1 it would read MR JOHN SMITH.

View 5 Replies
View Related
May 21, 2014

I have an excel spread sheet with the page title "Main". I would like to add a macro by pushing a button and a window pops up. It will have 4 columns or 4 questions. The first question will ask what is the date? You enter date, next question asks, What is the fundamental? You enter the fundamental. Next question is what is your name? you enter name and next question is enter what you need fixed. Once you enter that and press finished, the data is then place in the spreadsheet. All rows shift down 1 spot. B2 answer is posted in B2, question 2 answer is placed in C2, question 3 answer is placed in D2 and question 4 is answered in E2.

Each time someone presses the NEW ENTRY button this window pops up asking these 4 questions. The answers are then posted in cells B2 through E2 after shifting the entire page down 1 row.

View 2 Replies
View Related
Jul 22, 2014

I use a software to find relationship between some variables. this software can export results (excel mathematical formula) to a text or excel file. I need use this formulas in my VBA codes. but I should convert about 100-150 formula each time, hence this is very time consuming and hard to do that manually. I wondering if there is any macro code or third party software for that?

excel formulas are like this:

VB:

=((1/2)*((((EXP(EXP(EXP(-0.00159049260456001*$A1)))+2.36277505628942)/(LN(1.34876400300668*$B1))+(1/2)*((13.8900474312246)+(EXP(2.22254189839997*$C1)))))+(2.56256412775789*$D1/((1/2)*((1.34876400300668*$B1)+(EXP((1/2)*((EXP(-0.000633515788838835*$E1))+(-15.3575339156491)))))*(-15.3575339156491-1.31506071323009*$F1*EXP(EXP(2.22254189839997*$C1))))))*0.00300997948851126+-0.00736105020728091)

View 5 Replies
View Related
May 8, 2014

I am about to take on a rather large excel project for our company. The first part is coming up with how to deal with our drafting department's length inputs. In this project, our users will be entering lengths in a unique format. I have to convert that unique format to a standard Foot-Inch-Fraction format.

To try to keep the file size down and keep the complexity down, converting a cell formula to VBA so i can run this on a group of cells rather than repeating my code downward multiple rows on multiple sheets.

It is either this or come up with my own font that will convert it for me but I do not know where to start with that. How to take a stab at converting this to a VB code i can run?

A3 = 1.0108

B3 = 1'-1 1/2"

The code in B3 looks like this:

[Code] .......

An explanation of the unique format we use in our drafting department:

Basic Feet, Inch and Fraction format = 0 . 00 00 = Feet . Inch Fraction

The numbers left of the decimal point are feet. The first two numbers to the right of the decimal point are inches and the next two are sixteenths of an inch.

Examples:

4â€™-2" = 4.0200

15â€™-5 1/4" = 15.0504

4â€™-11 3/8" = 4.1106

0â€™-1 15/16" = 0.0115

View 5 Replies
View Related
Feb 17, 2010

I have about 600 patients' charts, which I audit every 3-6 months. I have created a macro which imports each patient's workbook into a single workbook (one worksheet per patient). Now I'm trying to create a macro which will import each patient's most recent audit results onto one worksheet. Here's how the results are organized:

2/1/2010 Audit 3/1/2010 Audit 4/1/2010 Audit

Y N N/A Y N N/A Y N N/A

x x x

x x x

...etc., with each audit taking up 3 columns and 32 rows. My code, however, is not working,

View 9 Replies
View Related
Feb 11, 2014

I need a macro code to add and rename sheets as per data given in column "A".

ie if column A has

SBC

DFG

UY

IKJ

4 new sheets to be created with name given above (Column A). PS:- the data given in column A will not be limited to 4 rows, it ll be > or < 4.

View 9 Replies
View Related
Apr 24, 2014

I have 3 list of data and I need to combine them.

Item list (5 to 100 items in a list)Quantity listStore list (2 to 10 stores)

For every item in my list, I need to add the list of stores to distribute to, and the quantity needed.

Example of order sheet

Stores

Items

74

22

70

71

59

[Code] .........

I need a macro to convert the order array list into column form such as Items A, Store B and Qty C

Item

QTY

Store

1001

2

74

[Code] ......

I have been trying for 3 weeks different loop combinations.

View 2 Replies
View Related
Sep 7, 2006

I have to convert a range reference in the form of

Range(Cells(1,1),Cells(2,2))

to a reference in the form of

"A1:B2"

View 5 Replies
View Related
Sep 11, 2007

I have many customers particulars and data coming in everyday. How i do create a code such that with one click i'll be able to update my worksheet in Excel without typing in manually?

View 10 Replies
View Related
Jun 5, 2013

Collapsing function: I want to select cells that I want merged, and have them COLLAPSE into the first cell selected calls

Combining function: OR Select the cells I want merged, and allow me to pick a destination cell for the result COMBINE

I would like to add this functionality to all my spreadsheets in the future.

Excel-Forum-Data Collapsing sample.xls

View 1 Replies
View Related
Jun 11, 2014

I need a VBA code to create a Text file which should be in the form of DML (my scenario is to get Insert Statements for the Data available in the Excel) from Excel Sheet. And this has to be done by assigning a macro to command button where by clicking on it the DML text file should be generated. Even the syntax for the scenario will do.

View 1 Replies
View Related
Apr 20, 2009

I am using the following Control Shift Enter (CSE) formula to find a value.

View 4 Replies
View Related
Mar 2, 2008

convert the formula into macro

I hv formula as following :-

=Left(B2, Find(" ", B2, 1) - 1)

I likt to convert into macro,

ActiveCell.FormulaR1C1=_ "=Left(B2, Find(" ", B2, 1) - 1)"

But it cannot work

View 9 Replies
View Related
Sep 17, 2003

Is there a quick way, other than VBA, to put the contents of a cell that has the text of a formula into a second cell, but as a real formula?

What I mean is, if A1 has as text: abs(-1), I would like cell B1 to read the contents of A1 and evaluate it and put the results into B2.

View 9 Replies
View Related
Oct 31, 2006

The attached has a sumproduct formula that is slow down the calculation of my workbook. (I know there is also an array formula-that's another thread). I'd like to use a macro to fire on the worksheet change so I don't have the heavy recalc burden. The formula is in worksheet "Database" in column H. So far:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range(Range("rReason").Offset(1, 0), Range("rReason"). _

Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then

Target.Offset(0, 1) = Application.VLookup(Target, ValList.Range("ReasonLkUp"), 2, False)

'This formula below needs a VBA equivalent

' Target.Offset(0, 2).FormulaR1C1 = "=IF(RC[-1]>0,SUMPRODUCT(-(R7C7:R35000C7<0)*0.5,--(R7C3:R35000C3=RC[-5]))+SUMPRODUCT(--(R7C7:RC7>0),--(R7C3:RC3=RC[-5]),R7C7:RC7),0)"

End If

If Not Intersect(Target, Range(Range("rSurname").Offset(1, 0), Range("rSurname"). _

Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then

Target.Offset(0, 1) = ActiveCell.Offset(0, -2) & " " & ActiveCell.Offset(0, -1)

End If

End Sub

View 5 Replies
View Related
Oct 31, 2006

The attached has a mega array formula that really slows down the recalc on this spreadsheet. I would like to use a macro to write the value of the formula to the worksheet "Database" column J.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range(Range("rReason").Offset(1, 0), Range("rReason"). _

Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then

Target.Offset(0, 1) = Application.VLookup(Target, ValList.Range("ReasonLkUp"), 2, False)

'This formula below needs a VBA equivalent

' Target.Offset(0, 4).FormulaArray = "=IF(MIN(IF(R7C3:R35000C3=RC[-7],IF(R7C8:R35000C8>0,R7C8:R35000C8)))=RC[-2],MIN(IF(R7C3:R35000C3=RC[-7],IF(R7C8:R35000C8>0,R7C8:R35000C8))),0)"

End If

If Not Intersect(Target, Range(Range("rSurname").Offset(1, 0), Range("rSurname"). _

Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then

Target.Offset(0, 1) = ActiveCell.Offset(0, -2) & " " & ActiveCell.Offset(0, -1)

End If

End Sub

View 9 Replies
View Related
Nov 16, 2006

Can anyone create a vba macro with the condition mentioned below

=If(Or(( COUNTIF(F2,"*Error*")>0),(LEN(F2)<4), (COUNTIF(F2,"*html*")>0)),"Fail","Pass")

I tried to do but of no use.

View 9 Replies
View Related
Aug 2, 2014

I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.

View 8 Replies
View Related
Jan 29, 2010

I have the following VBA code, which works nicely -

PHP For Each C In Sheets("data").Range("A2", Range("A2").End(xlDown))

C.Offset(0, 5).FormulaR1C1 = "=IF((RIGHT(RC[-1],3)=""ago""),RC[-1],"""")"

Next C

What I want to do is Paste As Values to each of the cells in the above to convert the formula to a static value,. Is there a simple bit of code I can include in the above loop, instead of doing a Selection.Copy Selection.PasteValues etc over the range?

View 2 Replies
View Related
Oct 13, 2006

Have a macro that copies a formula from each of 100 workbooks to a new workbook. I want to display these formulas as text and want a macro or someway to display these cells as text. I have tried to record a macro that presses the F2 key, the home key and the apostrophe. This works for the one cell but provides the following macro that does not work for anyother cell.

ActiveCell.FormulaR1C1 = _

"'=VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)"

Range("B3").Select

View 2 Replies
View Related