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
ADVERTISEMENT
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
View Related
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?
Variables:
MARM_fileNM
MARC_fileNM
MAKT_fileNM
Temp_fileNM
View 3 Replies
View Related
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
Nov 10, 2006
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View 5 Replies
View Related
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
May 24, 2013
I am trying to write a script to import, reformat and save sales files for upload into our master server and had a question.
how to make it store a range as a dim to use later. I would use
Code:
Range(ActiveCell,ActiveCell.End(x1 Down)).Select
on all the columns, but there are often blanks in many of the rows and I am worried about potential errors.
There is a column which always has all of the rows populated, so I was hoping to could store the range of that column and use it for all the others, so they all match length.
View 2 Replies
View Related
Feb 7, 2013
Refining the below code which repeats itself for 60 agents per worksheet, for 7 worksheets. Is there a way to loop the code so it counts up 68 cell references until the 'blank cell' condition is met?
[code]'## Agent 1
If Sheets("CSR Dashboards").Range("M3").Value = "" Then sResult = MsgBox( _
Prompt:="CSR Dashboards have been sent.", _
Buttons:=vbOKCancel)
If sResult = vbOK Then
[Code] ........
View 9 Replies
View Related
Nov 15, 2008
Thanks for all the help so far with this issue. I seem to be getting errors after running a macro a second time. The name of the cell range is changing. I have attached the spreadsheet and I will try to be specific as possible.
Goal 1: I need to input monthly data into the Sales tab and then save it as a CSV file.
Goal 2: Re-open original xls file that has data then Run macro to create reports.
Goal 3: Save Original xls file as a clean sheet to use again next month.
Process:
1 - Insert Data into Sales tab
2 - File/Save As a CSV (Now the CSV file is open)
3 - Close CSV file and re-open the original xls file
4 - Run Macro to create reports
5 - Print Reports
6 - Manually delete current data on all tabs so I have a clean sheet for next month
7 - Save and Close
Problem:
When I open the xls file (next month) to input new data and run the reports I get a Run Time Error '1004" The Pivot Table field Name is Not Valid
I found that the Named Range of cells changed from the original:
View 8 Replies
View Related
Mar 21, 2008
I have a range Named "MyRange" which consists of cells "A4:H20". What I am attempting to do is when the user goes to save and or close the workbook it checks that all the cells within the range have been filled with data.
View 3 Replies
View Related
Apr 6, 2009
I have previously tried to save the whole original workbook with the following
ActiveWorkbook.SaveAs Filename:="C:UsersPhilDocumentsMy Excel DocumentsRostersIndividual Rosters" & StaffLastName & " " & StaffFirstName & " - Roster commencing " & Format(DateSerial(Range("X11").Value, Range("V11").Value, Range("T11").Value), "dd mmm yyyy") & ".xlsm
but receive a run-time error message:
Run-time error '1004': Microsoft Office Excel cannot access the file
My first request for help is how is the code wrong and how can it be corrected?
Secondly, instead of saving the whole workbook, is it possible to save just a given range of cells as a new workbook, using the format above to give it a personalised filename?
View 9 Replies
View Related
Mar 11, 2008
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
View 3 Replies
View Related
Jul 17, 2014
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
View 1 Replies
View Related
Jul 17, 2014
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
View 1 Replies
View Related
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
Jan 27, 2012
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
View 8 Replies
View Related
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
Jan 25, 2013
I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")
I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.
the first two lines of code work fine, the msgbox shows "E100:E105", happy days!
However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.
VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup
MsgBox Range(thisString).Address
Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)
View 1 Replies
View Related
Jul 16, 2009
I am using the code below to copy a range and paste it over a variable range.
View 4 Replies
View Related
Mar 6, 2008
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
View 3 Replies
View Related
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
Oct 25, 2007
I have a range "C1:Cx" where i want x to be the value from a value "overallLastRow". How do I write this?
View 9 Replies
View Related
Oct 26, 2008
This is just a simple question but I can't seem to find the answer in any of the hepl files. I want to refer to a range by using a variable but can't get the syntax right.
for example instead of
View 2 Replies
View Related
Feb 19, 2009
I am in need of code to sum the above range. But it is different range every day. My company will add the deposit amounts each day from each deposit sheet.
ie:
48000
80000
12000
3000
200
45000
10000
And so on. I have posted an example spreadsheet if needed. (The portion is green is all that needs to be summed but keep in mind it changes amount of rows included changes each day, and the yellow is where the solution goes, but I can easily change that). There are other formulas needed but I have figured those out. I need to run this macro daily just for the last group.
View 6 Replies
View Related
Mar 14, 2012
I have a problem of setting a range of row that might expand regularly.
I tried a simple method but...
Code:
' Count the used range row
Dim count As Integer
Dim usedrow As String
count = ActiveSheet.UsedRange.Rows.count
usedrow = "C" & count
' The items are in A1:A105
Set AllCells = Range("C4:usedrow")
View 2 Replies
View Related
Mar 7, 2008
I left my Excel books at home, so I can't even look up the answer, and it's incredibly frustrating, since I know it's something small that I'm missing.
I've got a code that I want to cycle thru 12 ranges, named "Retrieve1" thru "Retrieve12". This is the snippet of code that I'm having the issue with is:
For I = 1 To X
NextRange = "Retrieval" & I
Range(NextRange).Select
Application.Run Macro:="EssMenuRetrieve"
Next I
I only made the "NextRange" variable because I originally had "Retrieval" & I in the range select statement and it didn't work.
View 9 Replies
View Related
Dec 9, 2008
I am using this coding
LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Range("A" & Row.counts & ":" & "D" & Rows.Count).Copy Destination:=Sheets("Sheet2").Range("A" & LR + 1)
And it keeps giving me the error "Object required"
I am trying to copy from A-D and the row is a variable to which row was checkmarked at that time.
View 9 Replies
View Related
Nov 6, 2006
I am trying to create a spreadsheet that will predict the quantity of a product sold in a given month based on the number of new clients that month and the frequency with which the existing clients will need that product to be replaced. E.g. Washing Up Liquid is required by every new client and they will need a new one each month. Toilet Cleaner is required by every new client and they will need a new one every 3 months.
I have 'hard coded' the two examples into the formula on the "Non-Variable" sheet. However the Frequency of Replacement is speculation; it may traspire that a bottle of Toilet Cleaner only lasts 2 months, in which case I would have to go into each cell and change the formula to reflect this. I believe Add or SUM Every nth Cell may hold the answer but I am failing to get it to work with my data due to being a novice and not really understanding the formulae used! The second sheet is my attempt.
As you can see, it counts 3 months forward from the first month, not 3 months BACK from the CURRENT month. It also simply doesn't work: Washing Up Liquid should equal 285. Ideally I would like to keep the format of the 1st sheet as this matches the rest of the the workbook (not included).
View 4 Replies
View Related
Dec 23, 2006
still trying to figure it out how the logic in VBA works
'this selects a block of sells and put them in variable rormu. Right?
Set test = ActiveSheet. Range("i65535").End(xlUp).Offset(-1, -3)
Set test2 = ActiveSheet.Range("i65535").End(xlUp)
Set formu = Range(test, test2)
'here i try to create another variable for a range after last used row
Set newg = ActiveSheet.Range("f65535").End(xlUp).Offset(2, 0)
Set newg2 = ActiveSheet.Range("f65535").End(xlUp).Offset(3, 3)
Set pst = Range(newg, newg2)
View 6 Replies
View Related
Mar 8, 2007
I have reached a point in my code where I have a variable range selected. I want to autofill the selected range of data to a new range.
My code is:
Range(Cells(7, LastReportColumn - 1), Cells(LastReportRow, LastReportColumn - 1)).Select
Set DestinationRange = Range(Cells(7, ReqMonths + 5), Cells(LastReportRow, ReqMonths + 5))
Selection.AutoFill Destination:=(DestinationRange), Type:=xlFillDefault
The select part works. However on the second line (set DestinationRange) I get a type missmatch.
View 5 Replies
View Related