convert formula to VBA

I need some help figuring out how to convert formulas that I have
working in an excel spreadsheet into VBA language in Excel. I have the
following three formulae that do just what I want. I go through all
kinds of hoops to open excel, write this formula in a cell, copy it
then write it to a new spreadsheet. would be much faster if i could
capture the value of all three formulae in VBA. Suggestions
appreciated:

=max(h1:h300)

=(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))

=ROW(OFFSET(A1,COUNTA(A:A)-1,0))

What do I need to do to get each of those working. I think if I can
figure these out then the generic rules can be used to apply to other
examples.

Thanks in advance,

Rob


-- 
rroach
------------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21093
View this thread: http://www.excelforum.com/showthread.php?threadid=386564

0
7/12/2005 7:51:05 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
562 Views

Similar Articles

[PageSpeed] 0

here's one way - go to tools/options/general/select R1C1. Then, for each of 
these formulas, you may see something like
=MAX(RC[3]:r[299]c[3]) for example, depending on where the =MAX(H1:H300) was 
entered. In any case, drag across the formula in the formula bar, then in 
the VBA you can use:
Range("Whatever").FormulaR1C1 = "=MAX(RC[3]:r[299]c[3])"
Get the idea?

Oh -- revisit tools/options/general, and change R1C1 back! (deselect it)

"rroach" <rroach.1s2ovc_1121198770.5021@excelforum-nospam.com> wrote in 
message news:rroach.1s2ovc_1121198770.5021@excelforum-nospam.com...
>
> I need some help figuring out how to convert formulas that I have
> working in an excel spreadsheet into VBA language in Excel. I have the
> following three formulae that do just what I want. I go through all
> kinds of hoops to open excel, write this formula in a cell, copy it
> then write it to a new spreadsheet. would be much faster if i could
> capture the value of all three formulae in VBA. Suggestions
> appreciated:
>
> =max(h1:h300)
>
> =(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))
>
> =ROW(OFFSET(A1,COUNTA(A:A)-1,0))
>
> What do I need to do to get each of those working. I think if I can
> figure these out then the generic rules can be used to apply to other
> examples.
>
> Thanks in advance,
>
> Rob
>
>
> -- 
> rroach
> ------------------------------------------------------------------------
> rroach's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=21093
> View this thread: http://www.excelforum.com/showthread.php?threadid=386564
> 


0
7/12/2005 9:21:12 PM
Assuming where you want the formulas is B6, B7 and B8...

Range("B6").Formula = "=max(h1:h300)"
Range("B7").Formula = 
"=(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))"
Range("B8").Formula = "=ROW(OFFSET(A1,COUNTA(A:A)-1,0))"

HTH,
-- 
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''Yes'''' button next to 
''''Was this Post Helpfull to you?".


"rroach" wrote:

> 
> I need some help figuring out how to convert formulas that I have
> working in an excel spreadsheet into VBA language in Excel. I have the
> following three formulae that do just what I want. I go through all
> kinds of hoops to open excel, write this formula in a cell, copy it
> then write it to a new spreadsheet. would be much faster if i could
> capture the value of all three formulae in VBA. Suggestions
> appreciated:
> 
> =max(h1:h300)
> 
> =(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))
> 
> =ROW(OFFSET(A1,COUNTA(A:A)-1,0))
> 
> What do I need to do to get each of those working. I think if I can
> figure these out then the generic rules can be used to apply to other
> examples.
> 
> Thanks in advance,
> 
> Rob
> 
> 
> -- 
> rroach
> ------------------------------------------------------------------------
> rroach's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21093
> View this thread: http://www.excelforum.com/showthread.php?threadid=386564
> 
> 
0
gary_brown (76)
7/12/2005 9:29:06 PM
you can also copy a formula

  range("A1").formula =  range("A2").formula

  About the other's people ideas, I prefer to use the
..FormulaLocalR1C1
  or
..FormulaLocal

  because what you see in the sheet is written in your local language, 
but vba formula is in english
  With FormulaLocal it's the lcal name (auto converted)

the R1C1 just mean you are using the R1C1 naming of cells, but you do 
not seem to use it, so it's better to forget this R1C1 (and do not 
change your options in the menu, keep the way you like )

  VBA accept R1C1 or not R1C1 (just tell him which one you write)


0
7/12/2005 9:41:31 PM
thanks all.

next question is if I use:
Range("B6").Formula = "=max(h1:h300)"
Range("B7").Formula =
"=(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))"
Range("B8").Formula = "=ROW(OFFSET(A1,COUNTA(A:A)-1,0))"

for my 3 formulae, how do i set a variable to equal each of those
values for writing to a text file?

TIA,

Rob


-- 
rroach
------------------------------------------------------------------------
rroach's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21093
View this thread: http://www.excelforum.com/showthread.php?threadid=386564

0
7/12/2005 10:35:53 PM
who, take care of one thing: a string can not contains any "

  or you must double the double-quotes

  "abc""toto""def"
  means
   abc"toto"def

but
"=(CELL("address"

  if supposed to give  "=(CELL(" string followed by stuffs

> for my 3 formulae, how do i set a variable to equal each of those
> values for writing to a text file?


  I'm not sure to well understand what's your wish. Can you give an 
example ?
0
7/13/2005 1:08:20 PM
In VBA,

Dim strB6 As String, strB7 As String, strB8 As String
strB6 = ActiveSheet.Range("B6").Value
strB7 = ActiveSheet.Range("B7").Value
strB8 = ActiveSheet.Range("B8").Value

HTH,
-- 
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''Yes'''' button next to 
''''Was this Post Helpfull to you?".


"rroach" wrote:

> 
> thanks all.
> 
> next question is if I use:
> Range("B6").Formula = "=max(h1:h300)"
> Range("B7").Formula =
> "=(CELL("address",OFFSET(h1,MATCH(MAX(h1:h300),h1:h300,0)-1,0)))"
> Range("B8").Formula = "=ROW(OFFSET(A1,COUNTA(A:A)-1,0))"
> 
> for my 3 formulae, how do i set a variable to equal each of those
> values for writing to a text file?
> 
> TIA,
> 
> Rob
> 
> 
> -- 
> rroach
> ------------------------------------------------------------------------
> rroach's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21093
> View this thread: http://www.excelforum.com/showthread.php?threadid=386564
> 
> 
0
gary_brown (76)
7/13/2005 1:33:22 PM
Reply:

Similar Artilces:

Date selection from form VBA
Hi Everyone, I'm trying to run an append query with a criteria on a date/time field based on a start and end date-time that I compose in VBA on a form. The query has for criteria on the date/time field: >=[Forms]![oneoclock]![Startdt] And <=[Forms]![oneoclock]![Enddt] In the form VBA, as an example: Private Sub Form_Open(Cancel As Integer) Dim Startdt As String Dim Enddt As String Dim dummy As String Dim stSocName As String dummy = "31/10/2007" Startdt = dummy & " 09:00:00" Enddt = dummy & " 09:15:59" stDocName = "pull_test" D...

How to 'convert' photos saved in .doc format?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel My first post here - please be patient. I'm helping a friend who has just converted from her PC to a shiny new iMac and I've struck a problem that I don't know how to resolve. On her PC she saved many (hundreds) of photos in .doc format (don't ask)! She thought that was how to do it. She's adamant that she needs to keep all these images. <br><br>I've never faced the problem before and don't seem to be able to find any way of converting the .doc formatted pictures to e....

Convert Seconds and decimals to time
Hello, I'm exporting some information from a system called CMS. The problem I'm having is that it exports in seconds and decimals. For instance 3:19 (3 minutes 19 seconds) is exporting as 226.605636597. I'm trying to convert this back to 3:19. Any suggestions? Thanks in advance! Chuck -- bagoxc ------------------------------------------------------------------------ bagoxc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30084 View this thread: http://www.excelforum.com/showthread.php?threadid=497654 I don't see how 3 minutes 19 seconds ...

Converting this "French" currency format to "English"?
I found this neat coding in the custom number format box: _ * #,##0.00_) $_ ;_ * (#,##0.00) $_ ;_ * "-"??_) $_ ;_ @_ It produces this when there is no dollar amount: - $ I like this format, but of course, need to write the currency in the more standard English format since this particular sheet is in English and not French so that I should get this instead: $ - Dunceville me ... I tried everything I could think of but I can't get it to come out right. Every attempt gave me an error code with the kind suggestion to use the "built-in number formats" :oD :oP ...

Enter date into Formula
I want to have a date without using date function in another function, i.e., MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5 I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL? -- Thanks, Don On Sat, 16 Jan 2010 19:40:01 -0800, DRA <DRA@discussions.microsoft.com> wrote: >I want to have a date without using date function in another function, i.e., >MATCH(01/02/2010,OFFSET(CC1stDate,1,0):OFFSET(CCDateCol,CCRowLast-1,0),1)+5 > >I use the #01/02/2010# in VBA. Is there a simular notation in EXCEL? The DATE function ...

Insert formula even cells with data below it
Using this code Sub Insertformula () For Check = 4 To 40000 Step 2 If Cells(Check, "b") <> "" Then Cells(Check, "b") = "=IF(OR(I5="",J5=""),"",NETWORKDAYS(I5,J5,Holidays!A$1:A$39)-1)" Next Check End Sub My objective is to put this formula =IF(OR(I5="",J5=""),"",NETWORKDAYS(I5,J5,Holidays!A$1:A$39)-1) Into every even cell in Column B with data below it. However if you tried the macro you would see it does not work. If I could get any help thank you. Please tell ...

Assign shortcut key to VBA procedure
Hi, Would it be possible to assign a shortcut key to a VBA procedure like can be done in Word and Excel? I use Outlook 2003 (MultiLangual version). -- Mvg, Frans Follow the instructions at = http://www.slipstick.com/outlook/toolbar.htm#macro to add a macro to the = toolbar, then give it an Alt+ accelerator key by putting an ampersand & = in front of the accelerator character.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators,...

Stopping a formula once a condition has been met.
Cell A1 of my worksheet contains the following formula: ='[WTA Work History Overall.xlsx]Retirement Summary'!Retirement_Payment Cell A2 contains a date. Is there any way that the formula in cell A1 can be constructed so that once the date exceeds the date in cell A2 that the formula stops updating the value in cell A1 and just shows the value that was in place prior to exceeding the date in A2? A formula can only return a value so with something like the following: =IF(DATEVALUE("29/11/2009")>A1,"A1 expired",A1 If the date in A1 was before to...

VBA and Scheduled task in Outlook
Hi, how can I: a) pull the query from somewhere and send it by e-mail automatically. E.g. I want to take a temperature table from http://www.wunderground.com/history/airport/EHAM/2009/7/10/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA where 2009/7/10 is the date of yesterday put it into an e-mail and send it automatically each day at 8 o'clock? b) if this is not possible, how can I send an e-mail with the attachment c:\Documents\query20090710.xls each day at 8 o'clock? my boss wants this automatized, I know how to write the VBA code for Excel, but don'...

Trying to create a formula for adding and subtracting columns.
I have a class that keeps a balance of "money" they earn in school. Each week we add the total then at the end of the month they subtract thing s they bought. So I would need a formula that adds the total for four rows and then subtracts in the fifth row to give me a final total. I am new to excel and am hoping I will be able to do it without too much trouble. I am usually a pretty quick learner. Are you wanting only to make this calculation occur on demand at the end of the month? If your happy for it to just calculate as you go along then just use "=A1+...

help with formula please #2
Can anyone please help with this formula. If Sheet1 Column A = nothing(blank) And Column B = \\\\\\CC\\\\\\ Then Sheet 2 B7 = SumTotal. As a regular formula would be nice or VB code Hi Richard you want nothing at all in the whole of column A in sheet 1? do you want every cell in column B of sheet 1 to have \\\\\\CC\\\\\\ or should this (can this) only appear in one cell? and what range are you summing on sheet 2 in cell B7? Cheers JulieD "Richard" <anonymous@discussions.microsoft.com> wrote in message news:82ce01c477e1$77f0e740$a301280a@phx.gbl... > Can anyone please ...

Formulas to reference range based on data in column
I probably didn't title this too well. I have a spreadsheet with 500 rows of data, sorted by the date in column A. I want to extract certain data from rows that all start with the same specified date. Previously I used an IF formula but that necessitates having the formula in 500 rows which won't work for my purposes. I'm going round incircles trying to think how best to achieve this. If you apply a filter to the date column for the selected date you can then just highlight the visible rows and copy/paste or cut/paste them elsewhere (eg another sheet) to extract ...

Excel formula ?. Help please.
I am in a number draw based on the lottery. Each member has 10 numbers and there are just over 400 members. I would like to try and set up a spreadsheet wherby I can enter a number in cell A1 and all cells which contain that number change to an X. Can it be done ?. Thanking you in anticipation. This is not exactly what you asked for, but: Say you have a long list of numbers in column C and your X-out value in cell A1. Then in cell B1 put: =IF(C1=$A$1,"X",C1) and copy down This will repeat the list in column C and "X-out" the value appearing in cell A1 -- Gary'...

Formula results display only after pressing Enter key
I import data from a database as a csv file and then save it as an Excel 2003 workbook. The first column in the spreadsheet contains a long list of four character entries. I remove the first character of each entry by inserting a column next to Column A and using the following formula: =right(A1, LEN(A1)-1) Usually, I can copy or drag the formula to the following cells in the inserted column and the formula results are displayed. However, today, when I copy the formula to the other cells, the results for Column B1 are copied to all the cells in Column B. When I look at the form...

Can't convert unsigned int to void * error message
Dear : i have the following code : CStdioFile file("C:\\report.txt",CFile::modeCreate | CFile::modeWrite); file.WriteString("My infor"); SetFilePointer(file.m_hFile,NULL,NULL,FILE_END);// the Compiler issued error said : E:\Private\Files\Dlg.cpp(321) : error C2664: 'SetFilePointer' : cannot convert parameter 1 from 'unsigned int' to 'void *' file.close(); Why? this error generated in Visual Stdion 6 , the same code implemented in Visual Stdio 7 (Visual C++.NET ) run without errors .!!!!! what cause the problem? Regards Raed Sawalha wrote: >...

converting minutes to hours
Hello everyone...again Having trouble with the last formula I need for this worksheet. This one is a bit tricky. If the user enters in a whole number in cellA, say 1 (which equals 5 minutes), cellB has to display based on cellA, the value in hours(in decimal form). so if a user enters in 6 in cellA, cellB would display .5 hours. This is possible, right? Any help or suggestions would be apprecaited Mike Hi try the following in B1 =TIME(0,A1*5,0) and format the cell B1 as 'Time' or enter the formula =(A1*5)/(24*60) and also format as 'Time' -- Regards Frank Kabel Frankfu...

Need Help With Copying Relative Formula
ello and thanks in advance to anyone who can point me in the right direction. I am running Excel 2003 on a Windows 2000 machine. I have 2 sheets in one workbook. sheet 1 is data input sheet 2 is summary I want to put this into summary ......A...........................B..........................C 1...='data input'B1........='data input'B2......='data input'B3 2....='data input'E1.......='data input'E2.......='data input'E3 I have about 100 rows each of about 10 columns that I wish to do this with. I know I can do it the long way by hand b...

Converting string of info in one cell into multiple
I have one cell containing a street, city, state and zip that I want converted to 4 individual cells without manually entering each cell. Thanks, Becky Try Data/Text to Columns. Use "," as a delimiter, then a space character. If you have states with two-word names spelled out, do a search and replace first to replace them with one word names, then reverse it after you do the Text to Columns. In article <66979866-7EDB-4A72-994B-FC32C38ED62D@microsoft.com>, bbrowning711 <bbrowning711@discussions.microsoft.com> wrote: > I have one cell containing a street, cit...

Help for the formula
Dear All, Please i need the formula for colum D Status, it can display Pickcal or In or Out or Picked. Colum A Colum B Colum C Colum D Confirm Date scanin scanout Status Pickcal 17-03-10 11:38 In 17-03-10 11:38 17-03-10 11:49 Out 17-03-10 11:25 17-03-10 7:29 17-03-10 8:24 Picked 17-03-10 11:20 Picked Thans so much. Tr...

Have a formula print to different worksheet
In Sheet1, I have a list of 20 products, each with a unique referenc number in column A. In Sheet2, I have a list of every potentia reference number, also in column A. I need a formula somewhere i Sheet1 that matches each reference number to its counterpart in Sheet and copies the data from that row (Sheet1) to the correct row i Sheet2. The biggest problem I'm having with this, is to create a formula in on cell (or sheet) and have the result appear in another cell/sheet. Would appreciate any help... :o ----------------------------------------------- ~~ Message posted from http://www.E...

Help with a formula #2
Hi, I am trying to create a fairly simple formula where when the use enters a 3 digit country code such as CAN, excel automatically enters the part of the world that code is from. So basically I have 7 categories (South America, North America, Asia Pacific, South Pacific, Europe, Middle East and Africa with each having the relevent 3 digit codes assigned to them for example North America = USA, YYZ, RDU - the most has 25 codes) so when USA is entered, the following cell would enter South America. So two things I guess I need to do :- 1) Assign country codes to country regions 2) Use a for...

Converting telephone numbers
Hi, I have a 2 lists of telephone numbers - one that is in the form 4165551234, and the other that has the format 416 555 1234. I need to convert these to the same format, and cannot (unfortunately) do it through Excel as I have more rows of data in my database than are allowed in Excel. I'm using Access 97 if that makes a difference. Any suggestions would be appreciated. Thanks, Chris Hi Chris, You can do this with an update query. Set a criterion on the phone number field of Like "##########" (that's ten hash signs) and update it to Format([XXX], "000 000 ...

If formula nesting
Does any one know if there is a limited number of arguments one can nest in an IF formula? I have tried entering 14 conditions and it will not accept after the 8th one. It tells me I have no false value. Or is there something wrong with my formula? =IF(H45=43.9, "Needles",IF(H45=21.95,"Needles",IF(H45=62.5,"HP",IF(H45=65,"HP",IF(H45=67.5,"HP",IF(H45=70,"HP",IF(H45=130,"HP",IF(H45=135,"HP",if(h45=140,"HP",if(h34=995,"CD",if(h34=1295,"CD",if(h34=1695,"CD",if(H34=1990,"CD...

Vba Cell Address Question #2
No, I have a drop down menue that quaries the internet for stock dat based upon the stock selected in that drow down menue. Since the dro down menue has a control cell linked to it I would like to utilize tha number which is in D5 on the sheet as a of determining where to pu data, i.e., the closing price of the stock selected. What I am looking for is a way to reference a cell address with th value of another cell. Any ideas? Thanks, Michae -- MJSlatter ----------------------------------------------------------------------- MJSlattery's Profile: http://www.excelforum.com/member.php?a...

Collecting data through email using VBA
Hi, I know that the "Create Email" tool in order to collect data through email but I want to do it automatically. My database form includes VBA code in order to send automate emails. I'd like to make this email "special" in order to receive the replay into access and use the answer email. I'd be grateful if someone could help me with the VBA code. Thanks! This capability is included in Access 2007, if you are using the .accdb file format, although you still need to initiate the process (ie. it is not fully automatic). What version of Access are you using? ...