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
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 : ...
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 RelatedI'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]"
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
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))
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...
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(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 RelatedI'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 & ")"
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.
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.
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)
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
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,
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.
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.
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"
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 RelatedCollapsing 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
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 RelatedI am using the following Control Shift Enter (CSE) formula to find a value.
View 4 Replies View Relatedconvert 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
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.
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
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
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.
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 RelatedI 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?
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
