I am getting an 1004 error:
Application=defined or object-defined error here:
ws3.UsedRange.Offset(1, 0).ClearContents
Relevant
Sub Importer()
Dim LastRow As Long, LastRowSrc As Long, LastRowDst As Long, i As Long
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, C As Range
Dim rng1 As Range, rng2 As Range
Dim sPath As String
Application.ScreenUpdating = False
sPath = "C:Documents and SettingsDougDesktopTGSTGSFiles"
I have a worksheet on which i use vba Usedrange to determine all the cells that are being used, but it only picks up all the range after the worksheet has been saved. It seems that the "usedrange" property only holds the actual usedrange at the last time the workbook was saved.
Is this correct?
If so, is there another way to "refresh" the usedrange property without saving the workbook.
If not, is there another way I can determine what the usedrange actually is at any point in time?
I have a usedrange in a workbook, say a1:g150 - simple.
I want to loop through just the first row of this usedrange and delete any rows that are empty.
Now I am sure this is simple and i just cannot get my head working. I have managed to select the first row and loop through, but I dont want to 'select' I just want to loop.
How can i assign the range to a variable? I can get the number of rows via usedrange.rows.count, which would return say 150, but how do i then assign a new variable the range A1:A150? then I can loop by using "for each cell in newrange"?
macros in excel XP.
I written up a marco code to do series of calculations for me. I have one program that will use different matrix size (say 5 rows by 5 colums for one set of calcuations and open up another, but with 105 rows and 35 columns)
Now,
the way it works is the user input data in row 1 and column 1 and the marco does the following codes-
Dim numrows As Integer
Dim numcols As Integer
numrows=worksheets("template").usedrange.rows.count
numcols=worksheets("template").usedrange.columsn.count
thisworkbook. names.add "matrix",,,,,,,,"=!r2c2:r" & numrows & "c" & numcols
this code defines my range that will be used in the formulas further down the code.
the problem is that I test ran the code using the matrix=b2:ai105
and I was using the code for a run with matrix=b2:q77
but the code is giving me the range from the previous macro run (ie. b2:ai105) and want it to use the matrix based on new data size (say b2:q77).
I tried using activesheet.usedrange into the macro, but it does not work.
Basically I need to "reset" usedrange property so it uses the correct range every time.
I am working on a project that involves copying data from one sheet to another, then breaking all links to the original sheet. This part of the program is working fine. The problem comes when I try to put borders around the used range of cells in the new sheet. I want the border to only go down to the last row that contains text in column A.
Currently, the program is putting a border around all the cells that have text plus those that originally contained links (all the links are not always used). So even though I have removed the links from the new sheet, usedrange still includes these extra rows. This is no good for printing, since the links spill over to page two and I dont want that second page to print if there is no data.
So I basically need to know how to fix the problem with usedrange, or instead need a way of specifying the last row that contains text in column A. I have done a search on this and tried a number of different things, however none have worked so far.
I would like to draw the border for the cells with in the usedrange from column A to BM.
I have the below code, that I was using to border the column D alone.
When i change the range from "A:BM", i dont get the intended output.
Can somebody tell, what modification, I should do to get the desired output?
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
- Find a value on a sheet “ORM” in Column G5:G33
-Get the corresponding value of B5:B33 of that cell
-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.
-Place this canned remark in TextBox31
I used Sheets(1).Cells(1, 1).Rows.End(xlUp).Count instead of UsedRange.Rows.Count in this code , but it didn't succed with me. Why and how to do that
Dim i As Long, j As Long
j = 1
For i = 1 To UsedRange.Rows.Count
Sheets(2).Cells(j, "a").Value = Sheets(1).Cells(i, "a").Value
Sheets(2).Cells(j, "b").Value = Sheets(1).Cells(i, "b").Value
Sheets(2).Cells(j, "c").Value = Sheets(1).Cells(i, "c").Value
j = j + 1
Next i
End Sub
I have attached the .xls file.
The basic setup is as follows:
(1) the user inputs a number into cells B2:B6 (arbitrarily shown as 3,4,3 and 2)
(2) the user then inputs a series of values into the respective columns E, G, I or K (corresponding to examples A-D, respectively) with SPACING between each value corresponding to the value entered in B2:B6.
For example, the value "4" is chosen in B3, so values are entered in column G at time = 0,4,8,12,16,20....etc
As another example, the value "3" is chosen in B4, so values are entered into column I at time = 8,11,14,17,20,23...etc, ie. the starting point is not necessarily t = 0.
(3) once the values have been entered as described in point (2) above, a series of results are automatically calculated in columns F,H,J and L.
NOTE: I have omitted the formulae for the calculation and have just entered arbitrary (color coded for clarity only-i don't need color coding!) numbers alongside each inputted value for the 4 examples A-D.
OK, so what I want to extract from the table for each example is the paired time AND result values, and then show them in a new table.
I have used an OFFSET formula (originally suggested by "daddylonglegs") to do this, and the results i GET are shown in columns O-V.
HOWEVER, what i WANT is the output as shown in columns Y-AF.
So as you will notice, there are 2 problems:
(1) Example C in columns S/T: value in cell B4 = 3, but the first value entered in column I does not start at a multiple of 3, so no values are returned in columns S/T because the offset function only uses row 6 as a reference point. What i want is the result shown in columns AC/AD.
How do i write a formula which says, "look down a column, find the first cell that has a value in it, and then offset from that cell/row" ?
(2) Example D in columns U/V: since the first input in column K starts a t = 12 (ie, cell K18), then i have 5 rows of empty space in columns U/V. What i want is the result shown in columns AE/AF.
Lastly, i should add that i am not necessarily stuck on using OFFSET function, since i keep reading that it is quite volatile.
I am trying to work out how to nest offset in a code using search replace. The goal is to find a value from cell A1 (example) and replace the values in the cells next to the cell containing samuel and the cell containing samuel.
A sort of search and remove data tool if you like So for example:
[QUOTE]A1: "samuel" (the search criteria)
Search range is: B1:D400
(for example)
Cell B40 = samuel
C40 = Driver
D40 = year
So, the macro is activated and finds "samuel" in B40 and I would like C40 & D40 replaced with "" The code I'm using is below: (this is just replacing the cell containing the search criteria with "test". I would like to nest offset(0,2) & offset(0,3).Value = ""
Sub Macro1()
Range("B1:D400").Select
Selection.Replace What:=Sheets("Sheet1").Range("A1").Value, Replacement:="test", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Using Offset
My
Private Sub OptionButton1_Click()
Set primary = Worksheets("Monitor Testing").Cells(20, 4)
For Counter = 1 To 3
If primary.Offset(0, 4)
Any way I could SUM a YTD using VLOOKUP and OFFSET? In another worksheet, I need to reference Account Income #, go to the worksheet shown below, find the Account Income number, Offset and sum of the columns in that row to the YTD figure.
Attached is a sample : EXAMPLE.xlsx
Account
Income
9/30/2012
8/31/2012
7/31/2012
6/30/2012
5/31/2012
[Code] ......
EXAMPLE.xlsx
I'm trying to lookup a string in a column from another sheet and than return the row on which that string was found. I have two files, one in which my OFFSET/MATCH worked and another in which they didn't work properly.
My problem is this: I have multiple instances of a string in a column and i want to get all the info on that string (the rest of the columns). Let me explain in examples.
In column A on Sheet 2 i have
4 2 4
2 3 4
5 3 2
2 2 2
4 1 3
I need to have in Sheet 1:
4 2 4
4 1 3
Where the first 4 (bolded) is the reference value.
In sheet 1 i have a column A2 which is the reference. In column B2 i have
=MATCH(A2;'Sheet 2'!A1:A16051;1) - this worked on a file but returned a bad value on the other (donno why)
In the second file i used =MATCH(A2;'Sheet 2'!A2:A10552;0)+1 which returned the good value.
For the second value (row 4 1 3) i used =IF(OFFSET('Sheet 2'!A1;B2-2;0)=A2;OFFSET(Sheet 2'!A1;B2-2;0);0). This worked well on the first file but didn't work on the second.
I got the commands above from someone that used to update this file so i don't know exactly how they work
I am trying to create a formula to determine if my students made a point in high standers, which means if they got a level 3 and about in 2009 verse the student's 2008. Now if they stayed on the same level did they make learning gains. So what I need to do is look at the grade level of the student (Colum C), then 2009 Math Developmental Score (Colum J), and Finally 2008 Math Developmental Score (Colum M). So if the student scored a level 2 in 2008 and a level 3 in 2009 he gets a point, but if he stayed on the same level. The formula needs to look see if he made learning gains. So if the student is in grade 4 and was a level 3 in 2008 and 2009 he would need to score 163 points higher in order to get a point. I have created a helper table with the information need to calculate the information. I have been working on this for about a week I can’t seem to get it right.
View 3 Replies View RelatedIn row E10:Z10 I have a series of values and in row E6:Z10 I have dates associated with each of the values in row 10.
I would like to create a formula to look in E10:Z10 for the maximum value and then return the date associated with that value. For example, if the maximum value is in P10, I would like the formula to return the date in P6.
I have tried without success to use the OFFSET function as follows:
=OFFSET(MAX(E10:Z10),-4,0)
I trying to out together a small script which performs the following:
-Select cell B7 and offset this by 12 columns and one row
-Resize this by 14 columns, then
-Apply the font format to the selected range from the first to the last row
The code below is what I've put together so far, and although this runs without issue, it's not applying the formatting.
[Code] .......
I'm using the code below to perform the following:
Compare a column of project end dates (column G on the "In Flight Projects" sheet) with the dates in a row of column headers, and Where there is a match, count the number of projects which are being worked within that given month.
[Code] .....
The code works fine, but I'm having a little difficulty in making an amendment to the code:
In the following section of code I need to select the Range ("G8"), but then say if the value in the cell, offset minus 4 has the value of "C&R" then run the rest of the script :
[Code] .....
I've made the following changes, but I'm unable to get this to extract the figure despite not receiving any error message:
[Code] ....
I have a number (>100) of excel workbooks (consistent format) on a server and I am trying to create a master spreadsheet with links to specific cells. I started using formulae based on Concatenate and Indirect functions but always had to use F2 & F9 to get a result. I am now working on a macro to build semi dynamic links, I am some way there but need some expert help please.
In Col A I have the path and file name for all my source files - I have used the Filename *.xls & FoundFiles for this.
In Col B is my specific file name(s) extracted from Col A using an If , Len and Text function.
My macro declares variables; File = Range (“B#”) and Data(21) - an array of 21 specific worksheet cell references
I then select C1 and with active.formula insert the consistent path, File and Data(1).
Then C2 with path name, File and Data(2). Then C3 path name, File and Data(3). & So on for the 21 variables in the array - not elegant but it works okay
I am now at the point where I need to move down the rows and repeat this for each source.
I am wondering (hoping ) there is a simple way of offsetting this for the 100 rows / source files. I would also like to be able to set the offset repeat by counting the number of populated rows in Col A.
The code (from memory so syntax may be a bit wonkey):
I have a rather details and complicated procedure that in effect is designed to find a value (>=) than a text box value inputed.
There are 2 other list boxes (5 & 6) that are also designed to narrow down the search.
The values available in list box 5 are a customers name in column C.
(Sample sheet highlighted value in Yellow)
The values available in list box6 are a product type.
(Sample sheet highlighted in blue)
When i select a value in list box5 and in listbox6, i enter a numerical value into text box111.
I then click on a command button, to populate listbox3 with all found values in column C that are greater than or equal to the value in the text box.
Now comes the part i cannot seem to be able to do.
I need to be able to click on a selected value in list box 3, and find the value that is between 2-22 rows above it in column A and place this value in text box 4.
(List box 3 values are ONLY ever
I have used the Selection x1Down code to select all rows to the next one with data in but now need some code to deselect the final row (I want to delete all of the blank rows above)
View 2 Replies View RelatedI am trying to write two different codes to copy a range of data. Basically I am trying to understand how the OFFSET function works in VBA. The first code listed works fine and is as follows:
View 5 Replies View RelatedI am trying to OFFSET from cell A1 based upon a variable in cell A2. The cell I need to OFFSET to is also located in column A, but it could always differ based upon the variable in A2. Here is the piece of code performing this OFFSET.
View 7 Replies View RelatedI am having trouble with one line of this code that I can't figure out how to write correctly. I have set the range, but I need to offset by 10 rows up. My code fails at set rng.
View 6 Replies View RelatedI'm copying values from one workbook to another. Each value has to be checked, so I do this in two steps.
View 2 Replies View Relatedhow to make offset for the cells to the row -3 in colum B
View 9 Replies View RelatedI have 3 workbooks, the first one contains the data and the second is the “Table1” containing "Normal records" and the third is the “Table 2” containing only "900 Records". Tables 1 and 2 will be linked to Access…..
Here’s my issue….
From workbook 1, I would like to copy data from certain cells that contain data to Table 1
From workbook 1, I would like to copy data from certain cells that contain 900 service records data to Table 2
In workbook 1, I have identified which information i want to keep by using the following formula,
i have a table with customers and the rentals they have made. in the table it has their customer number and then the item code of the item they have rented out.
i want to have a table that brings up the item codes of what they have rented but with a vlookup it only brings back the first item they have rented and not the ones after that.
=IF(MATCH($A7,$BX$15:$BX$112,0)>150,"",OFFSET($BX$14,MATCH($A7,$BX$15:$BX$112,0),1))
If there is not match, I get #N/A in the cell. I would prefer to get a value of zero (or ""). I've been experimenting with ISNA and ISERROR.