Define Name -variable- By Saving As

Feb 16, 2010

I am looking for a solution to define a name by saving as. I think I'm almost there, but can't find the part where i can name it.

In steps:

1) Define the path and the name of the current file as string - to delete extension (=.csv)
2) Save current file as current name with .xls in folder where is current file saved before.

This is a part of my code - the part that is neccesary to see what I am doing: ...

View 8 Replies


Define Range As Variable?

Feb 23, 2013

why this won't work.


Dim DRange As String
Dim ERange As String
Dim SRange As String
EndRow = Range("A65536").End(xlUp).Row
DRange = Range("D1", "Z" & EndRow)
ERange = Range("E1", "Z" & EndRow)
SRange = DRange

View 6 Replies View Related

Using Variable To Define End Of Range

May 19, 2008

how to define the end of a range using a variable determined by a rng.Rows.Count command (or anything else)? Is this even possible? Here is what I currently have:

Set rng = Range("A1:A10000")
For i = rng.Rows.Count To 1 Step -1
If rng.Cells(i).Value = "0" Then rng.Cells(i).EntireRow.Delete

I'd like to define the end of the range with more accuracy than I'm currently doing.

View 9 Replies View Related

Define A Index With The Use Of A Variable

Jun 8, 2006

i wrote the following statement that worked:

'Selection.FormulaArray = "=UserDefault!R[-27]C[-1]:R[-23]C[1]" '

but i need to define the index with a variable

'dim franz as integer

franz = 89

Selection.FormulaArray = "=UserDefault!R[franz]C[-1]:R[-23]C[1]" '

this does not work, is there a possibility to do this? the index from where te values should be read is calculated in the macro, so i can not use fixed values.

View 3 Replies View Related

Define A Variable Range

Dec 22, 2006

I am searching for data, upwards from cell A1400. Once I find data (cell > 0), I need to count up 27 rows and then select these rows for columns A to E. Then I want to copy this block of data and paste it. I have been having difficulty with the selection of a block of data which will be in a different position depending upon the data used. The code I am using is below. The two lines that I need to replace with a Variables cell range reference are:


Sub CalculateATR()
If ActiveCell.Value > 0 Then
Exit Do
ActiveCell.Offset(-1, 0).Select
End If
Loop Until ActiveCell.Value > 0

End If
End Sub

View 8 Replies View Related

Define Path & Filename As Variable

Feb 16, 2007

Private Sub cmdImport_Click()
Dim exportFile As String
exportFile = "c:jpmimportTrinity_ImpImport_" & Format( Date, "dd-mm-yy") + "_" + Format(Time, "hh:mm:ss") & ".csv"
Open exportFile For Output As #1

I put a watch on exportFile and it's correctly set to: "c:jpmimportTrinity_ImpImport_15-02-07_17:55:01.csv"

However if I try to run this I get "bad file name or numer" when I open for output - the path exists and I can write to it, so must have a fundamentally wrong approach.

View 2 Replies View Related

Define A Cell Range From Variable Values?

Jul 21, 2009

I have a range staring with cell "A5" and going to Column "K8+" that I need to copy. My issue is that the number of rows to copy will change. I have the number stored a variable "a" in my coding. how do I code this to work?

View 5 Replies View Related

Declare & Define Variable As Date Range

Nov 13, 2006

I'm fairly new to VBA and I need to define a variable as a date range
i.e. Period1 = 1Apr2006 to 29Apr2006, Period2= 30Apr2006 to 27May2006 etc

View 9 Replies View Related

Automatically Define A Variable Range Using A Macro

Nov 25, 2009

1. I need to define a variable range of all the USED cells in a column (starting at row 2 as the row 1 is the header)

2. I need to do this for columns A through to P inclusively (all using the same lastrow reference as that of column A)

3. I need to call the range name by the contents of the header in that column (Row 1)

4. Some of the data in the header row (row 1) contains spaces - can we change this to an underscore for the range name only?

5. Does a range only work for the active worksheet? Because i will have multiple worksheets, all with the exact same information in row 1, but different numbers of rows (and different data as well), but i will need the range defined on each worksheet......

I've found the following code and thought it was it, but it only defines on 1 worksheet, and for some reason only defines ranges for columns 1 and 2. Also it defines the range as A1:A65535 - not the USED range.....

View 9 Replies View Related

Define Pivot Table Source As Variable

Dec 28, 2007

I am producing graphs that compare last years figures (static) with this years figures ( Dynamic) and this years figures come from a Pivot table and Worksheet that has been imported. The value returned in each cell for the graph's dataset is derived from a specific Pivot table row and the column name (month) that matches the column of the data set - so far so good. The formula currently looks like this.

=IF(ISNUMBER(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),0)

where 'Count PS' is the name of the worksheet and Q3 is the reference cell that holds the Month Name. The problem is that I will not always be creating the report and other users may use a different name for the imported worksheet. I want to be able to set the name of the worksheet to be referenced in a supporting worksheet so that the user could simply amend the cell value to be the name of the source worksheet, but I can't work out the syntax

View 2 Replies View Related

Saving A Range Or Row As A Variable

Jun 17, 2009

Sub Testing()
Dim r, LR As Range
Dim k As String
Dim i, Total_Hours, Employee_Row, Last_Row As Integer

i = 0
Employee_Row = 5

For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
i = i + 1

If i 1 Then

If k = "" Then
'First Line
Total_Hours = Range("J" & r.Row).Value

I'm saving LR.Range ("A" & r.Row - 1) into LR as I need to use it outside the loop for the last row here

Sheets("Sheet2").Range("B" & Employee_Row).End(xlUp).FormulaR1C1 = Range("A" & LR.Row).Value

It seems I cannot save a range like thateven using LR as an interger and doing LR = r.Row does not work.

View 9 Replies View Related

Close Workbooks (with Variable Names) Without Saving

Mar 7, 2014

I have completed a long macro, and at the end of the macro I want to close a few workbooks automatically without saving. These workbooks have been assigned variable names.

Is there a command line I can use, that will not prompt the user to save?


View 3 Replies View Related

User To Select Variable Folder For Saving Files

Jun 11, 2009

I would like my macro to prompt the user to select a folder for files to be either opened from or saved to during the running of my macro.

In fact, I need them to select two folders, one for this month and the other for last month.

I think I need to set the two folders as a variable but being fairly new to vba I'm not quite sure how to do this.

View 9 Replies View Related

Saving A File Without Saving Macro

Jan 30, 2003

I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?

View 6 Replies View Related

Saving User Inputted Data From A Worksheet To Another Worksheet And Saving It

Jan 7, 2009

I have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.

I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"

On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.

What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.

I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.

Attached is my excel file.

You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.

View 9 Replies View Related

Define Value Between Two Dates?

Sep 23, 2012

1. I am entering the prices of a hotel. And the hotel has different prices in different periods.

For ex: 03/07/212 - 04/06/21 128$

I don't want to define it as an array in Sheet1. I want to enter start date A1 and end date to A2 and the price A3. And to the next column next period definition and so on...

2. After I define all dates like this when I will create a date array at Sheet2 in A column, I need a formula to make excel call the values to column B automatically to the proper dates.

View 5 Replies View Related

Define Name Question

May 13, 2009

I created three large formula that together give me the nested if limit if I try to put it all in one cell. Therefore I defined a name for each formula and then created another formula using the names =IF

(Facil_Move,less_25_Miles,Greater_25_Miles) my problem is the cell with this formula will only display answers from the last portion of the formula. I want it to display each answer as the various conditions are met. So it should be an answer for "Facil_Move" or "Less_25_Miles" etc.

View 9 Replies View Related

Define And Average

Jun 11, 2009

I have a different set numbers each day I need to average if they fit a certain criteria. These are actually times (in Military time) and in the 3 column spread sheet I have in column D the difference between column B and C. What I need to do now is find all the data from column A that is in between 1100 and 1500 and average all their column C results together.
I need to do this for all 6 4 hour time frames (from the small table).

I am using this array: =AVERAGE(IF(B2:B14>1100,IFB2:B14

View 9 Replies View Related

Define Range() In Vba

Jan 23, 2007

I want to define a Range() in VBA. have cell1 and Cell2 in the format of rows and cols. ie. Cell1 = Row 1, Col 2. Cell2 is dynamic, can be sometimes row100, Col200, or Row23, Col 1000. May i know how to define it in VBA?

View 2 Replies View Related

How To Define Dictionary To Access By Key

Jul 9, 2014

I have dictionary defined as series of keys and let's say two values:

...and so on

I would like to define named range from "name" column allowing user to select desired name from combo on another sheet. This is easy

But after that I would like to get val1 and val2 for selected name and show them with some calculation; For example to construct two columns like this: <nameX_selected_from_combo>, (<val1X>+<val2X>)/2

All the problem is how to select values from the same row as name selected in range.

View 2 Replies View Related

Define A Cell As Text

Jun 28, 2009

i need to define a cell as text. How can this be done? The reason for this is because if I type 001 in a cell it will automatically turn that into 1. Also this formatting would need to be copyable. So if I copy and paste that cell template and put in say 002 it would still read 002

View 8 Replies View Related

Define A Time Range

Jan 21, 2010

I have two columns containg the arrival and departure hours of workers. From these columns I must define the workshift by specifiyng the time range for each shift. AZ contain the arrival hours while BA the departure.

The formula I use doesn't return the results correctly because some shifts are almost the same. i.e: if shift one starts (arrival hours) between 05:00 and ends at 12:59 and shift 1-2 starts at 08:00 and ends at 23:59 then it will go with the first shift even though the times in range belong second shift.

View 8 Replies View Related

Define Variables In For Loop?

Jul 8, 2014

I am trying to define my variables with a for loop and if I run the code to the line after the first variable is defined, it shows that the variable is equal to the appropriate value, but after the for loop is done all of the variables are empty.

[Code] ......

View 5 Replies View Related

Define Name To Current Selection

Jul 25, 2008

I've tried Add Name code but it's not working the way I want. It always refers to the same range like for example

View 12 Replies View Related

Define Name To Current Selection ...

Oct 31, 2008

Define Name to current selection....

View 3 Replies View Related

How To Define A Cell For URL Input

Jul 3, 2012

I record this for retrieving some data from the web for a particular page

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;", Destination:= _

how I can make the URL be something in a particular cell on Sheet2 - cell B1?

View 3 Replies View Related

VBA Add Define Name To Refer To Last Row And Last Columns

Nov 7, 2013

with the below code i want the highlighted in red (RC11) to refer to last column and last row.

Sub try ()
Dim finalrow As Long
Dim finalcol As Long
Set wspo = Worksheets("Previous Order")
finalrow = wspo.Cells(Rows.Count, 1).End(xlUp).Row
finalcol = wspo.Cells(1, Columns.Count).End(xlToLeft).Column


View 2 Replies View Related

Define Workbook Name After Opening?

Jun 2, 2014

I have a code that opens a workbook and then calls a function on the workbook. What I want to do is reference that workbook so that when the function ends I can make that the active workbook again.

This is my code

Sub BarcLinkedCleanSort()
Dim fNameAndPath As Variant, wb As Workbook
Dim ws As Worksheet


View 1 Replies View Related

Define Name For Lines/shapes

Feb 10, 2008

Is there a way to define a name (Insert-->Name-->Define) for lines and/or shapes drawn using the Drawing toolbar?

I'd like to assign a specific name to a rectangle I've drawn outside of the sheet print limits. By using an If-Then formula, I'd like to bring that shape into a specified cell based on the conditions of the If-Then statement.

View 9 Replies View Related

To Define Userform Objects

Jul 16, 2008

I have a userform which interfaces with a worksheet as a "front end" and includes a GetData macro that retrieves the relevant data from the worksheet based on the ContractNum value keyed into the ContractNum text box on the form as follows (in part): ...

View 9 Replies View Related

Copyrights 2005-15, All rights reserved