Adding Variable To Autofill

Jul 9, 2014

The cells A2:A25 are merged in my excel sheet and I would like to create subsequent merged cells below the A2:A25 set; however, I would like the user to determine the number of merged cell boxes.

The code I have written is as follows:

Dim lr As Long
lr = Application.InputBox("How many days was the monitor deployed?", Type:=2)
If lr = 0 Then Exit Sub Else lr = lr

Range("A2:A25").Select
Selection.AutoFill Destination:=Range("A2:A" & lr * 24), Type:=xlFillDefault
Range("A2:A" & lr * 24).Select

End Sub

So I am creating a macro where the user opens a message box and types in a value representing the number of days. That value is then calculated into an autofill equation but I keep getting an error.

View 7 Replies


ADVERTISEMENT

Autofill With Range That Is Column Variable And Row Variable

Apr 4, 2008

I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this

View 9 Replies View Related

Autofill To Variable Destination

Aug 30, 2009

I have the following code that was achieved using the macro recorder. The only problem I am running into is that during the autofill command, there are never the same amount of rows to autofill. It could be as few as 5 rows and as many as there are rows on the spreadsheet. When I recorded the macro there were 953 lines, and unfortunately I cannot figure out how to change the specific destination of 953 to a variable destination determined by when data ends. Here is the code I currently have:

View 5 Replies View Related

AutoFill With Variable Range

Nov 1, 2006

I have worksheets that refernce values in one column off columns in others and do this on daily basis. each day there are blank amounts in random fields which I inturn use a macro to delete rows with blank amounts

I then want to autofill the numbers from 0001 to the bottem line of the sheet where the word "END" always is, as the position of the end word is random each time and autofill always needs a definite range how do I get my macro to autofill down to the word "end" ?

here is the code that doesnt work for me and I've been trying to fix

Sub Macro1()
For counter = 1 To 30
Set curCell = Worksheets("Sheet1").Cells(counter, 6)
If curCell.Text = "END" Then Range(Cells(1, 6), Cells(counter-1, 6)). _
Selection.DataSeries Rowcol:=xlColumns, Type:=xlAutoFill, Date:=xlDay, _
Trend:=False
Next counter
End Sub

View 3 Replies View Related

Use Variable For AutoFill Range

Oct 12, 2007

I am creating a loop that will autofill 16 cells down. It copies the formula from one cell (Z230), pastes that 16 cells down (cell Z246), then changes the lock properties of the cell (Z246) before autofilling down 16 (to cell Z261). At which point the loop starts over again. (copies cell Z246,pastes it to Z262, fills down 16 , etc)

Because the cell I am copying from changes in each loop, I set a variable to grab the address of the starting cell and last cell of the copy.

I then put this into a String so it would return something like this

Range("Z245:Z259")

Problem is I get extra quotes around Range("Z245:Z259") - it comes out as string "Range("Z245:Z259")"
This gives me a problem when I try to replace

Selection.AutoFill Destination:=Range("Z245:Z246"), Type:=xlFillDefault

with


Selection.AutoFill Destination:=rangevariable, Type:=xlFillDefault

Below is my total code, but I get stuck on the selection line. I have to do this 300 times so I would prefer not to do it manually!

View 9 Replies View Related

AutoFill Based On Variable Last Row

Jan 20, 2008

What I need to be able to achieve is a function in my macro that will

a: Count the number of rows in the active sheet
b: Allow me to use this info in other functions, such as subtotals, autofills

After searching many sites I'm pretty certain the function I need to use is "rowcount".

I've managed to incorporate a loop using this function, which is fine when running the macro but not so good when you are stepping through 5000+ rows

RowCount = ActiveSheet.UsedRange.Rows.Count
For x = 2 To RowCount
Cells(x, 42).Select
ActiveSheet.Paste
Next x

So for me now it seems as though "x" should represent the number of row that has been counted in the above code. But when I try to use "x" in functions the macro falls over

Range("AQ2:AX2").Select
Selection.AutoFill Destination:=Range("R2C43:RxC50")

View 3 Replies View Related

Autofill With Formula: Variable Lengths

Oct 3, 2007

need to autofill collum C with a formula related do collum B. If I do this by hand I just type the formula in C 1, and click on the right-down corner of the cell selection, and the formula goes until the last line (last value on collum B). But I want to create a macro to this function, and the problem is that the files that I will apply the macro have different lengths. I want to modify the macro to be able to run from C1 until the end of the values on collum B. I don't want to freeze the last value.

Here is the macro

Sub Macro3()

ActiveCell.FormulaR1C1 = "=60000/RC[-1]"
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C4819")
Range("C1:C4819").Select
Range("E7").Select

End Sub

In other files C4819 will not be the last value, could be 5345 for example, but its impossible to do this, my macro should do: "autofill collum C with a formula until the last value on collum B"

View 9 Replies View Related

AutoFill Code With Variable As Row Number

Aug 7, 2007

Just a niggling problem, I've got lstRow as a Long and it contains the value of the last row offset by (1, 0). The problem is i'm trying to add it into a range

Range("C500:K500").Select
Selection.autofill Destination:=Range(C" & lstRow &":K" & lstRow &")Type:=xlFillDefault

Like that.. Except i've tried a million different combinations of " and & in different places to try and get it to compile. It refuses too. It always gives the error "Expected list seperator or )" I know I could just do it individually from C to K but I'd like to learn how to do it this way as well.

View 6 Replies View Related

Autofill Macro For Variable Cell Data

May 13, 2006

I'll do what I can to explain this mess I want to clear up...I have a series of excel reports I have to download and work thru daily and I need to see if there is a code to autofill down thru a column, based on multiple statements throughout the column..here goes:

f1 msp
f2 msp
f3 blank **
f4 mct
f5 mct
f6 mct
f7 blank **
f8 cci
f9 blank **

Regardless of the actual f cell, I always will need to fill the Blank ** cell w/ the values from the previous cell....Hope this makes sense...& thanks in advance....
have_a_cup@cox.net

View 5 Replies View Related

Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?

Feb 19, 2010

I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.

The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?

View 6 Replies View Related

Adding A Variable Range

Nov 20, 2008

Per attached I have a range of columns (variable) which I need to sum, the number of rows are also variable. I have defined the variables:

firstrow
lastrow
firstcol
lastcol

I have tried the following code which isnt quite working.

View 3 Replies View Related

Adding A Variable Rows Worksheet

Mar 31, 2009

Due DateInvDocument numberDebitCreditBalance
17/09/08L01607610714211201825.44
13/10/08L01637010284345841020
13/10/08L01637131713874501020
13/10/08L016373852202473461020
13/10/08L0163748.5222E+111020
15/10/08L01641811103746832316

The above is a part of a w/sheet. Rather than determining the last row in the debit column and credit column (to know which is greater) and use a loop to put a formula in each of the cells in the balance column (adding the debit and credit), I know I can use the with ... statement to input the formula at a go , but cannot figure it out.

Sub sumtotal()
With Sheets(1)
With .Range("G14", Range("G" & Rows.Count).End(xlUp))
.Clear
.Formula = "=sum(E14+F14)"
On Error Resume Next

End With
End With
End Sub

View 9 Replies View Related

Number Variable: Keep Adding 24 When The Value Return To 0

May 3, 2007

I have few range of data that may range from 0 to 24 and the return to "0 " after 24 and the 2nd set of value keep repeating 0 to 24, but I would like to keep adding 24 when the value return to 0 and if the number less than 5 will be adding 24 and the number from 6 to 8 will be equal to 11 and also the number from 9 to 23 will not change. I have attached an example that Colum A needed to be converted to column B (which I done it manually).

View 2 Replies View Related

Adding Currency To Variable Range Of Cells?

Oct 24, 2012

I'm trying to create a sum that gives the output with the currency format but also change column D from from D2 to the Total the currency format. (it changes based on how many customers). Here is the code

Code:

Windows("Test1.xls").Activate
Dim rng As Range
Set rng = Sheets("Sheet1").Range("D65536").End(xlUp).Offset(1, 0)
Range("D" & lastrow + 4).Select
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(lastrow + 1, 4).Formula = "=sum(D2:D" & lastrow & ")"
rng.Select ("D" & lastrow)
Selection.NumberFormat = "$#,##0.00"

I know it has to do with the rng.select statement but I'm just not sure how to make it work for all of Column D from D2 to the Total/Sum.

View 2 Replies View Related

Adding A Constant To Sets Of Rows Of Variable Size

Jul 30, 2009

I have data from 100 trials. Each trial has a variable number of data points (rows), but each row is labeled in the first column with the trial number. I would like a macro to identify the first row of each trial, calculate the difference of the value of the cell in column G of that row from a constant (718), and then add that difference to that cell and all other cells in that column of that trial.

So based on the attached sample, I would like all values in column G for trial 2 to be +1, for trial 3 to be -5, and for trial 4 to be +2.

In the file, I've cleared all the data from the impertinent columns. Eventually, I will also want to perform a similar process on column H but with a different constant.

View 2 Replies View Related

Adding Variable To Specific Sheetname In A Sumif Function

Feb 4, 2010

Hi, looking for help desperately in fine tuning a formula. I have a formula at the moment (which works) for searching through a list on a separate file and totalling up all values which relate to it, see below:

=sumif([filename.xls]1’!$B:$B,D10,’[filename.xls]1’!$H:$H)

The tab ‘1’ in the formula relates to the first of the month so this month there are 28 different tabs with similar information.

With C10 containing the date in this instance, does anybody know a way of making ‘1’ a variable so that entering ‘04/02/10’ would change it automatically into a 4? (Unfortunately for me changing the 1 to =c10 didn’t work).

View 14 Replies View Related

Adding Time: Adding Individual Cells Works, But SUM Doesn't

Mar 28, 2008

I have a column of times: e.g. 10:03:00 and I would like to add them all up.

=A1+A2 works fine.

=sum(A1:A10) does not.

View 14 Replies View Related

Adding Dim'd Variables: Ignoring Strings & Adding Number

Jan 13, 2007

I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.

I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.

View 9 Replies View Related

Adding Data With Userform: Check For Duplicate Before Adding

Feb 14, 2007

I have a userform that I'm using to add data to a worksheet, with the following

Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")

OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
......
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.

IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?

View 9 Replies View Related

Set Variable: Object Variable Or With Block Variable Not Set

Jun 4, 2007

I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"

Sub test()
Dim StartRng As Range
Dim Buffer As Range

Set StartRng = WorkSheets("Sheet1"),Cells(1,1)

StartRng.Activate
ActiveCell. CurrentRegion.Select

Buffer = rngStart.CurrentRegion.Copy

' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub

View 9 Replies View Related

Adding Values In Cells (stops Adding After Row 14)

Oct 1, 2009

I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?

The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?

View 5 Replies View Related

Adding Measurements Together And Adding Letters After Totals

Dec 18, 2011

I am needing so I did it as a picture. (Please assume "Day 1" is A1)

View 7 Replies View Related

Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions

Aug 8, 2009

On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.

View 4 Replies View Related

2007 Macro: Run-time Error 91:Object Variable Or With Block Variable Not Set

Feb 20, 2009

I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...

The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.

View 9 Replies View Related

Summing Variable Values Across Variable Sheets In Multiple Columns

Jun 27, 2014

I need a macro that will create a sheet at the end of the workbook.

Sum data from a variable amount of sheets and display that data on the created sheet.

Here is a step by step:

Starting on sheet 5.

Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.

Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.

(Note: The data would also need started on row 4. Everything above row 4 is headers)

Here is a small example:

D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1

I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).

After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.

So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).

View 2 Replies View Related

Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time

Dec 7, 2008

I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.

View 3 Replies View Related

Excel 2007 :: Object Variable Or With Block Variable Not Set Error

Feb 28, 2013

I Wrote a code which as intended to open each excel file in a folder and copy the data containing in it into a new sheet.

But While running the code the first excel file gets open, and an error message "Run Time Error 91-Object Variable Or With block Variable not set Error"

How to set the file which got opened from the folder to wbk variable.

Code:
Sub dataintoonesheet()

Dim i As Integer
Dim jk As Integer
Dim j As Integer

Dim rowstart As Integer
rowstart = 3

[Code] .......

View 9 Replies View Related

Macro Error Message (object Variable Or With Block Variable Not Set)

Jul 7, 2009

When i try to run the code below i get the error message - object variable or with block variable not set-

Sub REFRESHXX()

'LIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
'SET RANGE
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing

With Sheets("Points")
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
End With

'FORMULA IN R1C1 STYLE
strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
'ENTER FORMULA IN ALL CELL RANGES
r.FormulaR1C1 = strFormula
'REDUCE TO VALUES
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Next ar

'UNLIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1

End Sub

View 9 Replies View Related

Find Method Code: Object Variable Or With Block Variable Not Set

Sep 8, 2006

I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow

Here is my

Sub test()

Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long

'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'for anyof the two lines down I get the message "object variable OR block variable not set"

lRow = rFoundCell.Row
lCol = rFoundCell.Column

End Sub

View 5 Replies View Related

Find Dates Macro: Object Variable With Block Variable Not Set

Nov 21, 2006

I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select

End Sub

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved