Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Add Formula & Auto Fill Via Macro Code


I want a Macro to be able to copy a formula down from cell A2 down the entire column within the data range. But, the problem I've got is that the spreadsheets I'll be using the Macro on will be different sizes. Currently, the VBA looks like this:

ActiveCell.FormulaR1C1 = "= LOWER(RC[3])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A17340"), Type:=xlFillDefault
Range("A2").Select

So the "Destination:=Range("A2:A17340")" part copies the formula into all cells from A2 to A17340. Other spreadsheets might only have data up to cell A200, or Awhatever - is there a way of getting the macro to populate just the cells in column A but only where there is data adjacent in other columns?

Also, I want the Macro to be able to delete all rows wherever "DELETE" appears in a certain column - I had a look at the "Delete Entire Row Based on Criteria" Macro but I'm looking for a fully automated solution, rather than the question boxes coming up and asking which cell/criteria, I need to run this macro on multiple sheets and the criteria/column position will always be the same -


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Add Formula To Cell & Fill Down Macro
I have written a macro that creates a new spreadsheet and copies text into it. The number of rows varies depending on the input.

Yet, running the macro results in a sheet with the max rows (65k+). This is unmangeable, as it makes it a huge file and size and harder to print.

I only want there to be the number of rows that have text in them.

The larger problem is how these were even created in the first place. Currently:

' Formula for Job Name
Range("A2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[12],8)"

' Fill Job Name
Range("A2").Select
Selection.AutoFill Destination:=Range("A2", Cells(Rows.Count, "A")), Type:=xlFillDefault

View Replies!   View Related
Copy/Fill Down Formula Macro Code
I have created a macro with the below formula in it:

Range("L2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L234")
Range("L2:L234").Select

I am using column K and it looks up the length of this cell. When I created the formula I was using info that only went to line 234 which was fine. Once I clear the info in column K and input new info it can go further than line 234. How do I get the macro to look up the whole of column k without having to create a new macro each time?

View Replies!   View Related
Add Multiple Formulas In Row & Auto Fill Down
I am trying to do canít really be recorded, at least i donít think it can. I have a report that I run off and after I loaded it in Excel I then have to add in five columns at the end of the sheet. These are always in the same columns: R,S,T,U,V.

These are the formulas I have to then input
R =IF(G2="no invoices",A2,"")
S=IF(I2="Match",A2,"")
T=IF(I2="Sent to AP",A2,"")
U=IF(I2="Force Settled",A2,"")
V=IF( COUNTIF($R$2:$U$10054,A2),A2,0)

Note for column V, the range R2:U10054 would depend on how many rows is in the report, its just in this example the report does have 10054 rows. The columns would always stay the same. I would then copy the formulae down for the number of rows that is in the report and filter column V by entries that equal zero to give me the rows I am looking to analyse. Is this the type of thing that can be written as a macro so that everytime I have a new report open in Excel I can just run the macro and it will do all of this for me?

View Replies!   View Related
Auto Fill Off Of Zip Code Entry
If I have 3 fields: zip code, city and state; is there a way where I can have the user of my form enter their zip code and it will populate both city and state. I have the data, I just dont know exactly how to code it.

View Replies!   View Related
Add Formula Via Macro Code With Variable As Sheet Name
I have a front sheet with a list of all the sheets in the workbook in column A.
In column B I need to use the counta function to count the number of entries in the corresponding sheet to column A.

As there are 70 sheets I'd thought I'd use a Macro but I'm having great difficulties. I've done a search but can't find what I'm looking for.

So heres part of my code (it uses loops etc which I've managed but this just errors)

cell.Offset(0, 1).FormulaR1C1 = "=counta(" & str2&" C[-1])-1"

The str2 is the string name to reference the sheet in the formula (for example sheet1), I can't figure out how to enter this into the code so it works and is accepted.

View Replies!   View Related
Enter Only Part Of A Zip Code And Have The Rest Auto Fill-in
i have an excel sheet where i enter company information including Zip code. the zip codes around here all start with 797 and end with numbers from 01 to 12

i am looking for a way to enter just the last 2 numbers and have it automatically insert the full zip

example:

line one i would enter 01 and 79701 would show up in the box.

I know there are ways to do this, just can't remember its been so long since I've had to use excel.

View Replies!   View Related
Auto Fill Formula Using $
I created a database and made one small mistake doing it. The database is infinite amount of rows down and my formula needs to be corrected as such. In the current cells: (C1:C1000) i have the formulas referencing cells $B1, but i need it to reference $B$1. In C2 I need it to reference $B$2, C3 $B$3 and so on. If I autofill, it obviously just keeps the following cells at $B$1 and i can't keep the dollar bill sign.

View Replies!   View Related
Add Nest Formula/Function To Cell With Variables Via Macro Code
I would like to use a varible Cell for the following forumula:

ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F$2,F$3,F$4,F$5,0,)/3600,1)"

I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).

View Replies!   View Related
Add Formula To Cell To Equal Product Of 2 Other Cells Via Macro Code
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 Replies!   View Related
What IF Auto Fill Date Formula
I would like to auto fill the present date if another cell of the same row is typed in and then lock that cell with the date.

For example. If someone types in cell A2 then I would like to auto fill D2 with the date 11/01/2007 and lock it.

If someone tomorrow types in cell A3 then the date would once again be autofilled in D3 to 11/02/2007 while D2 would still display 11/01/2007.

View Replies!   View Related
Auto Fill Within Macro
my macro simply activates a cell and all i want it to do is to auto fill downwards!
its intended to autofill down for about 100 rows but just cant figure it out!

but keep in mind the cell is always going to change so it cant be fixed

i.e this wont work:

Selection.AutoFill Destination:=Range("C95:C659")
Range("C95:C659").Select
ActiveWindow.SmallScroll Down:=513

because i need it to be autofilled from the CURRENT active cell only!

View Replies!   View Related
Macro Code To Add Sum Formula/Function, With Variable Rows, To Cell
I am having to copy and paste rows of data into a new worksheet where the rows sizes change and I am wanting to add a new row at the end of the pasted rows but with the sumation formula to add the relevant column

e.g copy range B14:AA17 with in this case columns E to AA holding the numerical values. Therefore I wish in cell E18 to sum the value of E14:E17 and so on ending with cell AA18 holding the sum of AA14:AA17

As these vary I have all relevant variables, Range to add sumation values to eg E18:AA18
Start Cell E14 and so on.

I tried adding "=SUM(x:d)" where x and d are vars relating the the column cell required eg x = E14 and d = E17

View Replies!   View Related
Auto Fill Formula Along Length Of Report
I'm trying to use the coding below to insert a formula down the lenght of the report but it is coming up with a run time error.

View Replies!   View Related
Auto-Fill Formula When Adjacent Cell Changes
what is prompting this formula to auto fill down when a value in an adjacent cell is entered.

In column A I enter an unformatted fax number.
In column B I use the following Text Formula:
"="("&LEFT(A20,3)&")"&MID(A20,4,3)&"-"&RIGHT(A20,4)

Someone in my office copied the workbook and now any time a value is
entered into the next available Column A cell. The cell in column B will automatically apply the above formula and format the fax number. The person who copied the workbook does not know how this is working nor does anyone else in the office.

There are no macros embedded in this workbook so no event is firing to cause this.

I have attached a small copy of the workbook called "Auto format1.xls"

How to use:
-Select cell B20 and not nothing is in it.
-Enter any 9 digits in A20 and B20 somehow
copies down the formula from B19.

how to duplicate this.

View Replies!   View Related
Macro To Auto Fill Dates In Column
I have a startdate F1 and an end date G1. Is there a macro that can list all dates by day within that range into column I7:down.

View Replies!   View Related
VBA Macro Needed For Auto Fill Using Another Cell Reference
Is there anyone who can suggest a solution to my problem below ?
Basically, what I need is a macro which would ideally work like this:

if D32 = 1, then the background colour of D4 should be red
if D32 = 2, then the background colour of D4 should be orange
if D32 = 3, then the background colour of D4 should be yellow
if D32 = 4, then the background colour of D4 should be green

And then I plan to use it for columns E, F, G

View Replies!   View Related
VBA Macro To Delete Rows/Columns, Insert Formulas & Auto Fill
I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.

Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"

UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................

View Replies!   View Related
Edit Auto Add Macro
I have a macro that allows me to type numbers in cells and it auto adds to the previous value... what would be nice is if when i hit enter the cell focus stays at the current cell and doesnt move the next cell down... that way if i wanted to enter multiple values into one cell i wouldnt have to touch the arrow keys to get back to the cell

e.g: i enter a value into A1 and hit enter, instead of the box focus moving to A2, it stays at A1

Heres my current
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("DATA")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value + oldVal
Application.EnableEvents = True

End Sub

View Replies!   View Related
Fill TextBox Macro Code
i have a userform named: usf_History.

i would like to refresh all the text boxes (they either refer to cells or are calculations)...?

View Replies!   View Related
Add Auto Filter (unique Records) Option To Macro
I have been modifying a workbook and the original macro will send to the sheet Results once the "Search" button is clicked, but I have found that this macro is displaying duplicate records. Can anyone help me put in an auto filter to find only unique records? My second workaround option is if someone can help me remove the go to/select sheet option from the "Search" macro so that a user will not automatically be sent to the Results tab and will instead hit the "Confirm Category Selection" button (which auto filters before sending the user to the Results tab).

View Replies!   View Related
Fill Range From IBM Reflection Macro Code
I'm working on some macros to fill and file paperwork from an IBM Reflection based system into an Excel spreadsheet. Here's the code I've written so far:

Private Sub cmdSend_Click()
Dim ExcelObj As Object
Dim ExcelWasNotRunning As Boolean
On Error Resume Next
Set ExcelObj = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear
DetectExcel
If optObligation = True Then
Set ExcelObj = GetObject("S:PublicVisual Basic FormsExcel FormsObligation.xls")
Else..............

The problem I'm running into is that the data isn't loading the first time through. The spreadsheet opens, nothing happens, Excel closes. If I attempt to run the macro again without closing the form and clearing all the variables it runs perfectly as many times as I want to run it (which isn't terribly useful, because I'm stuck putting the same data into the form over and over again). If I unload the form then I get the same problem: first run, no data transfered.

View Replies!   View Related
Auto Enable Macro Code
what the code to auto enable a macro? I have tasks running on my computer. Everytime they run, I have to click on enable macro. I thought there was a way to automate this, but I can't find the code......


View Replies!   View Related
Auto Sort Macro Code
I have a spreadsheet to create with columns A-I, this will be used by others when I am away and the problem is I dont want them to have to keep going into the data-sort option. Is there a way that I can get the items to auto sort into alphetical and numerical order across the columns? Personally I would be happy with sorting the columns manually each time it's accessed but I know the others using the program won't be!! How would it work if its poss? would it auto sort on saving?

View Replies!   View Related
Code To Auto Populate A Relative Formula Into A Column
I have a report that I run off a database and extract to Excel.

It has 3 fields: Title; FirstName; LastName. For example sake lets say they are in columns A, B, C. Now in D I have a fieldname of FullName and in D2 onwards the formula: =A2&" "&B2&" "&C2.

Now I currently manually type the formula and drag it down to the same row as the last entry in the first 3 columns so that I get the full title in one cell.

I wanted to know how I could enter the the formula above in column D so that it automatically does this for me?

I tried to record a Macro as a test for column D setting the field name in D1 and the formula in D2 and got the following:

View Replies!   View Related
Macro Coding: Add A Code To The Sort And Paste Macro That Will Open The Second Spread Sheet
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the

Windows("estimate sheet one.xls").Activate

View Replies!   View Related
Macro To Add CommandButton & Assign Macro Code
I written VBA code to create a new sheet and embed a command button on it. I want to assign a macro to this button but when I click on the button the assign macro button is greyed out. I have plenty of macros saved and the VBA code has finished running so why should this be?

View Replies!   View Related
Macro To Fill In A Formula In Each Row Where There Is A Value
I am looking for a Macro to fill in a formula in each row where there is a value. So if there is a value in row 1 Column A then place this formula into Column B and continue down the sheet untill there is not a value in Column A.

View Replies!   View Related
Macro To Fill Down Formula As Many As There Is Data
Let's say I have data from column A up to column J.

Columns M, N, O contain formulas which calculate value from column J. Since this sheet is a template, the number of rows of data vary from month to month, i.e. the amount of records I copy and paste to columns A-J vary.

I would like to have a macro which will fill down the formulas in columns M, N and O as many as there is data in column J. Example: Let's say minimum number of row is 100, so up to row 100, column J already contains formulas. But if the following month the number of rows is 150, I would like the macro to automatically fill down the formulas in columns M, N and O up to row 150. But if the following month, there is only 130 rows, the macro should fill down to row 130 only.

View Replies!   View Related
Code To Add Formula To Cell
I tried putting a formula into the below code but without any success.

mylogoff. Offset(0, 3).FormulaR1C1 = "=IF(WEEKDAY(RC[-1]=""6"",""17:30:00""+0,""18:00:00""+0)

It says application defined or object defined error.

View Replies!   View Related
Macro To Fill Column With Relative Formula
I want my column to be populated by formula using VBA. Let's say on row1, the formula is = SUMIF(mysheet!$L:$L,$A1&C$1&"09",mysheet!$M:$M). And on row 2, the formula is
=SUMIF(mysheet!$L:$L,$A2&C$1&"09",mysheet!$M:$M). And on row 3, the formula is =SUMIF(mysheet!$L:$L,$A3&C$1&"09",mysheet!$M:$M). And this is done up to the last row. How do I do this?

View Replies!   View Related
Automatically Add Cell Formula With Code
Cell b2 contains the formula
=IF( COUNTIF(B$1:B2,B2)=1,MAX(A$1:A1)+1,"")
I want cell b3 to contain the formula
=IF(COUNTIF(B$1:B3,B3)=1,MAX(A$1:A2)+1,"")
I can do this within the spreadsheet by simply highlight and drag down to autofill and excel updates the formula references as I need

However - because the sheet gets very large (its a sort of rough database)
I dont fill in all the formulae on a blank sheet, but each time a record is entered using a VBA form I have the following working code copy the formula

ActiveCell.Offset(nextline, 0).Copy
ActiveCell.Offset(nextline + 1, 0).Select
ActiveSheet.Paste

However I dont want to use 'Active' - because I have to work out what cell is selected before this is executed and then reselect it after, which also causes the screen to flash when the active sheet changes so I tried the following which does not work

Worksheets(" Analysis").Range("A2").Offset(nextline, 0).Copy
Worksheets("Analysis").Range("A2").Offset(nextline + 1, 0).Paste

because 'object does not support this property or method' on the Paste.

I can save the code in a string and modify the string to generate the new code, but I cant find a way to assign the new formula to the next cell.

View Replies!   View Related
Formula To Automatically Add New ID Number To Code
I would like to add something (formula) to the following code to automatically add a new ID number with each new entry. Some of the entries will have the same name but each time a new entry is added I want the ID number to increase by 1. Assume that the first entry is 0001.

View Replies!   View Related
Add Subtraction Formula To Cell Via Code
If I wish to amend the below code such that it must use the cell one row above minus the cell on the left, how should I go about it?

Eg,

If in cell C10, the formula is to use C9 minus B10.
If in cell C11, the formula is to use C10 minus B11.

mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]

View Replies!   View Related
Locate Last Used Row & Add Formula Via Code
How can I program a variable 'rowcountnumber', that would count the number of active rows, starting from row 10 ?

View Replies!   View Related
Add R1C1 Formula With Code & Variables
I am using follwoing vb code to enter formula to sum a range. However it is giving sum of different range.

Cells(Row, Column).FormulaR1C1 = "=SUM(R[" & a & "]C[" & b & "]:R[" & x & "]C[" & y & "])"

Where a, b , x and y are variables containg starting row, starting column, last row and last column value like a=19; b=3; x=24 and y=3. When i check the formula in that cell, it appears as Sum(F25:F30)

View Replies!   View Related
Code To Add Sheet Formula Reference To Column
I need to copy a range of values from sheet 3 to sheet1. When I recorded macro, I got the below code. But, instead of RC in the 4th line <ActiveCell.FormulaR1C1 = "=Sheet2!RC"> I need to pass values like A1,A20 etc.

Since the values range & column to be copied would be varying dynamically, (say for first iteration it would be A1: A20 & for second iteration, I need to copy C1: C20.) how to pass these variables to the macro and use it instead of the static "RC". Whatever column I specify, it should copy from that <column>StartRow to that <column> EndRow.

Sub Updtval(StartRow As Integer, EndRow As Integer, f As Integer)
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault
End Sub

View Replies!   View Related
Open/Add New Workbook Macro Code
I am trying to write code to open a new (blank) workbook while in an existing workbook (I am then going to pass data between the two which is easy). When I try to record the code to get the syntax for opening a new workbook it will not record any code. I also need to name the new workbook based on text in a cell in the existing workbook. I just saw before posting this that the code;

workbooks.add

will open a new workbook now I just need to name the new workbook based on text in my existing workbook.

View Replies!   View Related
Add Month To Date Macro Code
Suppose the current month is Feb-08. How will I be able to get the next and the next months in this format(i.e Mar-08 and Apr-08). I am able to get Feb-08 but unable to get the following months. i did lots of google search but was unable to find any examples..

View Replies!   View Related
Code To Add Nested IF & ISERROR Formula To Cell
Application. ScreenUpdating = False

For FormulaRemake = 2 To 2000
Worksheets("Data"). Range("D" & FormulaRemake).Formula = ""=If(ISERROR(Data!W"" & FormulaRemake - 1 & "")"" & ""=True,"",Data!W"" & FormulaRemake - 1 & "")""
Next
Application.ScreenUpdating = True

I'm having troubles with the syntax for the .Formula part. I read that when you do this you're supposed to double quote everything, except if you need a "" in it...?
Should look like this when done right: =IF(ISERROR(Data!W1)=TRUE,"",Data!W1) Also is there anything besides ScreenUpdating that will make this code run faster?

View Replies!   View Related
Add Custom Sheet Template Macro Code
I am trying to use the worksheets.add method with the Type specifier pointing to a file location containing a worksheet template.

View Replies!   View Related
Macro Code To Add Reference On Sheet To Next Worksheet
how to use VBA to write a formula in one sheet that refers to a fixed cell on the sheet to the right. The referenced sheet may have different names and it may be in a different order (i.e. not Sheet(2)) but it will always be one sheet to the right. I just want to have cell A1=(cell B2 one sheet to the right).

View Replies!   View Related
Macro Code To Add Formulas To Reference Different Sheets
How do I change the code so that the macro looks up the next sheet (instead of by sheet number, which is how the code was recorded) and transfers data to the summary page until there are no more new sheets. See attached zip.

View Replies!   View Related
Macro Code To Add More Rows To Existing Chart
I have managed to create something similar to what i am working for using an example from Lacher and Gant Charts. i am now stuck as I can enter more than 40 status as it then gives me an error. The following is the code: Can any1 highlight where i need to make any changes to stop the error from occuring:

Option Explicit
Sub CreateTimeChartData()
Dim vTimeData As Variant
Dim i As Integer
Dim sRoom As String
Dim vLastEndTime As Variant
Dim oSeries As Series
' set up
Application. ScreenUpdating = False
Application.DisplayAlerts = False
' create chart data worksheet
With Worksheets("TimeData"). Range("TimeList"). CurrentRegion
.Sort Key1:="Room", Key2:="Start Time", Header:=xlYes
vTimeData = .Value
Worksheets.Add
On Error Resume Next
Worksheets("ChartData").Delete..........................

View Replies!   View Related
Dynamically Add Controls To User Form Macro Code
I am trying to dynamically add controls to my user form based on some values in my cell. I am successfully able to create a text box dynamically but my label is not getting displayed. here is my code


Private Sub UserForm_Activate()

On Error Resume Next
If (ThisWorkbook. Sheets("Sheet2").Cells(1, 8) <> "FALSE") Then
Dim ctl As Control
Dim ctl1 As Control


Set ctl1 = Me.Controls.Add("Forms.Label.1", ctl1, True)
With ctl1

View Replies!   View Related
Add Secondary Chart X-axis Below Primary Via Macro Code
In VBA, how to add a secondary x-axis in the lower portion of the chart if I already have the primary axis in the upper part?

This code

ActiveChart.HasAxis(xlCategory, xlSecondary) = True

just adds it to the upper if I have the primary at lower.

View Replies!   View Related
Add Button To Sheet & Assign Macro Code
how I can write code to create a button on a worksheets and then link to another subprocedure.

I tried recording a macro and this is what excel so eloquently gave me:

ActiveSheet.Buttons.Add(384.75, 60.75, 79.5, 39.75).Select
Selection.OnAction = "CreateImport"
ActiveSheet.Shapes("Button 1").Select
Selection.Characters.Text = "Parse Deposits for Import"
With Selection.Characters(Start:=1, Length:=25).Font
. Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

This works fine, my only problem is if the button that is created is actually named Button2 or 3, etc. Is there a way to assign the new button to a variable, or would it be an object? this way it wouldn't matter what it was named.

View Replies!   View Related
Macro To Add Conditional Formatting Code To Variable Range
I am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage. My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.

View Replies!   View Related
Add AVERAGE Formulas To Cells Using Variables In Macro Code
I am trying to have the formula =( SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Put into cells through vba. What I did to get the formula is typed it into an excel cell to find the average of a group of cells that do contain blank cells. The formula brought out the proper results. So all I did is put the formula into vba and changed the appropriate parts. The range will not be the same of course, but there is what I have.

ActiveCell.Formula = "=(SUMIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "," & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & "))/(COUNTIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "))"

When I show a msgbox for ActiveCell.Formula (Msgbox activecell.formula), it shows me the formula as above - =(SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Except instead of the s:ranges, it shows $L2:$L125 (which is correct). The quotes do show up around the criteria in both the sumif and countif. I keep receiving an error. I put a msgbox err.description & ", " err.number dialog in. The error comes up as ", 0" (no quotes).

View Replies!   View Related
Auto-Fill
I currently have this code ( which works perfect ) that was written by someone else. It performs an auto-fill in column "A".

I need to learn how to adjust this code to do it for other columns, but do not know currently.

Maybe someone could highlight of BOLD the adjustments to direct the code to the column.

Sub fill_rows_A_4()
Dim arrTmp As Variant
Dim lngRow As Long
With Worksheets("sheet1") 'adapt
arrTmp = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
For lngRow = 1 To UBound(arrTmp)
If arrTmp(lngRow, 1) = "" Then arrTmp(lngRow, 1) = arrTmp(lngRow - 1, 1)
Next
.Range(.Cells(1, 1), .Cells(UBound(arrTmp), 1)) = arrTmp
End With
End Sub

View Replies!   View Related
VB For Auto-Fill
I need it to autofill just as if you selected range A1 and A2 and double clicked that little square on the bottom right hand side of the cell (so it stopps auto-filling at the last active cell in B)

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved