Converting Worksheet Formula To VBA Code
Jul 6, 2006
I'm having a slight problem as I cannot find a way to translate the following code from my worksheet formula to VBA: =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=1,A1,MID(A1,1,FIND("-",A1,FIND("-",A1)+1)-1)). The code takes a value from one cell and takes the part that I need. There are two main types of inputs, they are in the form:
aaa-aaaaaaa-aaaaaa
aaaa-aaaaaa
The lengths are variable, but for the sake of showing you an example I have simplified it to the previous. The formula takes the part of the string that is before and after the first hyphen and puts it in the cell next to it.
View 6 Replies
ADVERTISEMENT
Nov 10, 2011
I have a working formula that I want to convert to vba code... I only want to store the formula result in each cell in my range, not the formula itself. It is an ARRAY formula (entered with CTRL + SHIFT + ENTER) and it would be ideal if it could follow R1C1 format to keep it relative for each cell.
Here is the formula as it's entered in my spreadsheet:
=INDEX(PerfMetricTbl!$U$2:$U$250000, MATCH(1, (PerfMetricTbl!$A$2:$A$250000=$A2)*(PerfMetricTbl!$D$2:$D$250000=$D2)*(PerfMetricTbl!$Q$2:$Q$250000=$Q2)*(PerfMetricTbl! $R$2:$R$250000=Z$1), 0)))
I am not super familiar with using INDEX/Match combos which is why I am struggling with this one...
The end result should be:
For each cell in myRange
cell.value = 'Formula result here
Next cell
-OR-
If possible to fill the range in one step:
myRange.value = 'Formula Result Here, utilizing R1C1 relative reference... (this may be a stretch given the range is 55krows x 18 columns)
The problem at hand is I need to fill a range of approx. 999,000 cells with values and the values are a VLOOKUP with 4 matching criteria. So returning the value from Col U on my LookupTbl when the criteria in Col's A, D, Q, and R are an exact match to my values in A, D, Q, and Z:AQRow1.
View 2 Replies
View Related
Apr 24, 2009
Converting formula to static code. I am currently using the following code on a spreadsheet:
View 3 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
Aug 26, 2012
I wanted to know if there is any way possible to get vba to insert a formula in a cell to each worksheet in each workbook in a folder and then using loop to extract all the info from each worksheet of each workbook in same folder into a master workbook?
View 1 Replies
View Related
May 12, 2009
Wondering where this code can be adjusted to be much more efficient. Right now it is going through 1000 rows and 14 columns and it is taking about 10 minutes. I eventually need it to go through 6000 rows and 32 columns..
I already stored the HexToDec(Cells(x, 1)) result to variable H2D and it didn't really make too much of a noticeable difference.
View 14 Replies
View Related
Mar 27, 2014
I'm trying to convert my nested if statement below into vba code.
=IF(L27=15,"15+", IF(AND(L27>=5,L27
View 1 Replies
View Related
Jun 24, 2008
I created this code in Excel 2007 now i want to put it on my other users machines they are using Excel 2003 and i keep getting debug errors can someone help
Sub setup()
' Keyboard Shortcut: Ctrl+Shift+C
'add sheet
Sheets.Add After:=Sheets(Sheets.Count)
'format headers
With Range("A1")
.Value = "Job Name"
.Name = "Arial"
End With
With Range("A2")
.Value = "Quote #"
.Name = "Arial"
End With
With Range("A3")
.Value = "Job #"
.Name = "Arial"
View 9 Replies
View Related
May 21, 2006
I have very little experience with Excel. What I am trying to do is to convert a timecode in this format 00;22;21;00 that is 22 min 21 sec and 00 frames to a serie of numbers that I have hard time to figure out. Example
00;22;21;00 = 13410000000
I want to be able to paste the timecode from premiere 2.0 (00;22;21;00) into a Excel cell that would automaticly convert it to (13410000000) so that i can eventualy generate a .txt file that would look like this:
<Scripts >
<Script Type="URL"
Command="http://www.softfx.com/slide01"
Time="13410000000" />
</Scripts>
View 2 Replies
View Related
May 18, 2009
My question is about converting a Literal String to Character Code. I'm using the following coded InputBox. And it prompts the user for what characters to search for in a string.
View 4 Replies
View Related
Dec 26, 2009
I have a bit of code that is grabbing only the first 9 positions of an alpha-numeric string. The problem I am running into is that when the alpha-numberic value contains an "E", the code turns it into an exponential number in error.
For example, I have the following in column G: 01877E107000. When I run the code, it turns into an alpha-numeric value. The result should be 01877E107 (in column K). I tried using the text function within the code.
View 3 Replies
View Related
Sep 9, 2009
I have an Excel 2003 worksheet with all the data in it in Upper-case.
I found this VBA code which works if you change a cell.
View 6 Replies
View Related
Feb 1, 2007
I have a userform that reads in a list of names into a combobox. When you choose a name from the combobox it then shows the that persons details from a sheet containing all the names.
I have some text boxes that allows the data to be modified and returns it to the sheet which works fine. What i also want it to do it put the data from the form into a sheet for that individual. For example the name of Ford would go into the Ford worksheet.
How do i read in the value from the combobox on the worksheet and use it as a variable to use in my code. For example
Sheets(Value from the combobox).Cells(NoOfRows, 3).Value = Me.TextBoxChangePosistion.Value
*NoOfROws is the variable to find a blank row
View 4 Replies
View Related
Jun 3, 2014
I have an excel document saved in a SharePoint document library. I then have another excel document where I created a connection back to the document being saved on SP (went to data connections->add->browse for more->enter the SP URL & found the document) and then opened that connection up as its own worksheet (existing connections->chose my connection). In the original worksheet (saved on SP), there are about 20 columns that are formatted as numbers. However, when I open the connection up in the new document, half of these columns are still number formatted, but the rest are being displayed as text EVEN THOUGH they are technically formatted as numbers (I right click->format cells & they are formatted as numbers). I can click into a cell and press enter and it will "come up to speed" and enter into number formatting, but as soon as I refresh my connection back to the original document, they go back to their text formatting despite the fact that the connection they are drawing from has them saved as numbers! Very frustrating. Its not a SP issue because I recreated the scenario by saving the original document to my computer and it did the same thing. I even went through one column in the original document and made sure there were no spaces saved to trigger the column to go to text- nothing.
View 2 Replies
View Related
Jun 28, 2013
taking a spreadsheet that has vertical repeating info in Column A and results in Column B and converting that to another sheet in a horizontal list. The main problem is that the repeating info in Column A may or may not always be the same for every customer; therefore, when it is placed in a horizontal format some cells may or may not have results. I tried a arbitrary lookup. Here is the formula I used:
{=INDEX(INFO!$A$1:$B$300, SMALL(IF($A$1=INFO!$A$1:$A$300, ROW(INFO!$A$1:$A$300)-MIN(ROW(INFO!$A$1:$A$300))+1, ""), ROW(B1)),COLUMN(B1))}
That got me started. I am willing to use a macro. Here is some sample data:
Table 1 has a sample of the info:
IDENTIFIER
CUSTOMER INFO
2000
111111
[Code].....
View 8 Replies
View Related
Jun 9, 2014
I need a formula that converts time in the format of hh:mm to 00:mm. I only want the minutes.
View 4 Replies
View Related
Jul 10, 2008
creating a formula that would convert a string like 63008 (Mdyy format) to a format that Excel can read as a date
=IF(LEN(I2)=5,DATE(20&RIGHT(I2,2),LEFT(I2,1),MID(I2,2,2)),DATE(20&RIGHT(I2,2),LEFT(I2,2),MID(I2,2,2)))
In other words IF(the length of the cell value is 5, parse the date this way, otherwise parse it another way)
If I2=63008, this would output 6/30/2008
I want to convert this to a function since that formula is rather tedious to type out, but I am new to Excel programming
View 9 Replies
View Related
May 28, 2006
I have an excel work book where in one page (sheet1) contains formula types to be used based on a code. and in the other page (Sheet 2) when I enter the code I need it to get the formula from 1st page and then do the calculation in the second based on the code. Ex : Sheet 1
Code Formula
0 Cell A * CellB
1 Cell B - Cell A*5%
2 (Cell A + Cell B)*10%
In Sheet 2
Cell A B C D ..........
Price(A) Units(B) Code Amount
100 5 0 500 (100*5)
100 5 1 answer (5 - (100*5%)
100 5 2 answer (100+5)*10%
I hope you got the picture. The problem is I'm not sure how to take only the formula from 1st sheet and then use it in the 2nd sheet to do the calculation.
I wont mind using a form for this even.
View 7 Replies
View Related
Apr 11, 2014
I have a column with names, where i need the 1st letters of the Names to be displayed in CAPS. If its a Single letter also, the same should be displayed in CAPS
View 2 Replies
View Related
Jan 13, 2014
I have two sheets, the first called 'AF' and the other called 'POR'. For one of the columns in the 'AF' sheet I used the following formula - VLOOKUP(B2,POR!$A$2:$A$111254,1,FALSE) to retrieve a string consisting of 7 numerical digits (Ex. 4678451). Now if I want this result of 7 digits in the same 'AF' sheet to be converted to the word "YES" - how do I achieve this? (All the 7 digits numbers are different or unique). Is there some sort of REPLACE command I use here or some other means?
View 2 Replies
View Related
Jan 27, 2009
I have whole bunch of formula's added to cells by code. So far so good. How could I then set in macro code that the result is stored in cell as value rather than formula? Now when I go to cell you can see =PIBVSearch(.... yadda yadda yadda in the field above.
I would prefer to change it so that content of cell changes to it's value so there's no formula visible(and above all not stored to harddisk(LOTS of disk space lost. Whole project started to trim down 269Mb excel file into smaller piece! I don't want it to go back there too easily...) or in danger of getting updated which might take a very long time easily).
Code works so that as long as there's rows which haven't got update yet but do have product run code(or something like that. Not quite sure what that number is but I figure it's product run) after code reached that far(which it copied from another file). Then for each row it calls functions for each cell which are responsible for filling invidual cells.
So either way to replace content of whole row with values or invidual cells, I can insert both into loop easily though whole row after cells are calculated would be a) faster to add(just one place to add, not 30...) and likely faster to run(project is about getting the file to run faster afterall...).
View 2 Replies
View Related
Oct 6, 2008
I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel.
Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!$S$2:$S$9806, where 'Data_Q2 2008'!$B$2:$B$9806 = 'Metric Upload'!C2.
'Data_Q2 2008'!$B$2:$B$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.
View 14 Replies
View Related
Mar 14, 2014
I want a formula for converting a number to text
For example: 2550 ( two thousand five hundred fifty). I found one formula (BAHTTEXT) but it convert into Thai Language.
View 9 Replies
View Related
Mar 13, 2013
Looking for a formula to convert time (00:00:00 AM) to a general 4 digit number (0000). Example 08:30:00 AM ---> 0830. Tried using format cells but no luck.
I was able to convert a standard number to time using the following formula: =TIME(INT(H2/100),MOD(H2,100),0)
View 2 Replies
View Related
Apr 13, 2009
I have a paper-based scoring sheet that we use to determine a rating category. I cannot figure out how to convert this scoring sheet into a lookup region and use some type of LOOKUP formula to determine the rating. I have looked at the various Grading examples on the Internet that show you how to look up a grade, based on a score, but they only look up a grade, based on a single input (the score).
Here I have two values going in--the person's age and their score, and I need to look up the rating, which is the column heading.
Can anyone point me in the right direction?
I've attached a sample spreadsheet which shows the original scoring sheet.
View 7 Replies
View Related
Oct 21, 2011
I am using a spreadsheet which imports information from an access database then calculates prices based on that.
At the moment in my column "0" I am referencing the number values in column CT from my access database (which gives me a standard retail price)
Code:
=IF(AF5="","",IF(OR('Line list'!CT2="",'Line list'!CT2=0,'Line list'!CP2=0),"0",'Line list'!CT2))
And then I use the following formula to work out the correct prices wholesale prices for different countries based on that.
Code:
=IF(AL5>1,AL5,IF(AK5
View 5 Replies
View Related
Jan 22, 2014
I have attendance tables that has column Y showing the shift to be worked and column Z showing any absentees. I used the following formula to count the number of people off per period.
{=SUM(COUNTIFS($Y$5:$Y$29,AM,$Z$5:$Z$29,$W82),(COUNTIFS($Y$36:$Y$60,AM,$Z$36:$Z$60,$W82)))}
in the above formula AM relates to a list of morning shifts.
The problem I have now encountered is that the system at work is still using excel 97 - 2003 which does not recognise Countifs.
way to adapt the formula to be compatible with Excel 97.
View 7 Replies
View Related
Mar 13, 2014
Formula (or a chart) for converting centimeters to inches.
View 1 Replies
View Related
Feb 23, 2014
I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
View 6 Replies
View Related
Mar 31, 2009
--------------------------------------------------------------------------------
Ok i have a formula that i use to convert bond prices, which are in 32nds to numbers that excel can understand. I use the formula:
=LEFT(F7,FIND("-",F7)-1)+SUBSTITUTE(RIGHT(F7,LEN(F7)-FIND("-",F7)),"+","")/IF(RIGHT(RIGHT(F7,LEN(F7)-FIND("-",F7)),1)="+",64,32)
This allows me to type 101-16 in and excel recognizes this as 101.5
My problem is that my data I am copying into the sheet is often in 1/8ths. So the price will come in as 101-272, which means 101-27 and 2/8 (or 1/4).
I admittedly dont understand this formula and just use. Can someone help me tweak it to recognize the third digit?
View 11 Replies
View Related
