Selecting First Row (variable) In Table Using Macro?
Oct 2, 2013
I've made a macro and spend some time making it work across different sizes of data. The last issue I'm having is after the macro sorted out some data, that it will then delete, it has to select the first row and shift select down before deleting it. However, depending on how the data is sorted, the first row could be 9, could be 10 and so on. See below for my code.
Code:
Sub Macro1()
Windows("statistik.xls").Activate
Range("A8").Select
[Code]....
If the first row in my data set is not one of the 3 criteria ("Personligt ejede virksomheder", "Privat", "Reklamebeskyttet"), then the first row # will be 10 instead of 9. If the first two rows are not one of the 3 criteria, then it would be row 11 and so forth. How do I make my macro take this into consideration?
I'm trying to make a macro'd button that when clicked will select the cells A1:A?? where ?? is equal to the value in cell B1
B1 is a variable number that changes to be the proper amount of rows that I require selected.
It's always A1 down to A9 minimum and A1 down to A400 max.
Once selected I need the macro to copy the selected cells to the clipboard for another application to be able to paste that info.
So far I've been able to get the copy command to work and the range command to work but can't seem to figure out how to put the variable number from B1 into the range command.
I've successfully copied the array of equations using the VBA that Pjoaquin enlightened me with from my last thread. The outcome was Sheet2!A2:O2 being successfully populated with the equations from my first sheet... but here comes the problem: I'm looking to autofill A2:O2 down to the last record in Column P. But the number of records in this table is varable.
I am trying to enter code into a Macro that will select a range and then put a subtotal at the bottom of the range.
I have values in col D & E that I am multiplying together in column F. I then want the macro to select a range from F2 to the last Value in col F and then put a total in the next cell down (in col F). The range needs to be variable.
I have a workbook that generates sheets for each year based on selected criteria. It starts at Column H and goes too AH and beyond. When my loop reaches Z it errors out. I think this is happening because the code is referencing the column as ASCII. Here is the
Sub Test() Dim d As Date Dim yrint, i, num_years, fields, field_start As Integer Dim yrstr, crit1, crit2, left_column_range_fixed, right_column_range_fixed, left_column_range_var, right_column_range_var, left_column_range, right_column_range, cost_column, cost_column_var, cost_column_fixed As String left_column_range_fixed = "H" right_column_range_fixed = ":AH" cost_column_fixed = "2" crit1 = "=x" crit2 = ">0" d = Date yrint = Sheets("Overall"). Range("H2") field_start = 9 'changed from 9....................
I want to make a range variable ("testrange") that I can use to define a range of non-continuous cells and then use that range variable to select that range of cells in different worksheets within the same workbook.
I tried the below code (simply coloring cells red), but get an error when I try to select "testrange" after moving to the next worksheet.
[Code] .....
The next worksheet is also supposed to have the same range of cells colored red, but I can only get it to work on the first active sheet.
I would like to avoid having to repeatedly rebuild the range I want selected each time I move to a different worksheet.
I'm creating a pivot table using visual basic. for 1 of the titles in the column ("Organization") I only need to Select "Telford", "Kaiserslautern" & "Morocco" out of several other options. The code I've got at the moment only tells the pivot table not to show the ones I dont want as per below:
Code: With ActiveSheet.PivotTables("PivotTable2").PivotFields("Organization") .PivotItems("Printers KL").Visible = False .PivotItems("Printers TE").Visible = False .PivotItems("Valls").Visible = False .PivotItems("(blank)").Visible = False End With
Not all of these options are always in there so the macro crashes when it can find 1. Is it possible to de-selct all options then only select the 1's I want rather than the other way round using visual basic?
Book, Loaned to, Date A Tale of 2 Cities, John, 1/1/2010 Oliver Twist, Mary, 2/3/2011 Great Expectations, James, 4/6/2011 Oliver Twist, Greg, 6/2/2010
I want to make it such that, when I input the name of the book, it will tell me the person whom I lent it to last and the date of the transaction. Preferably, this should be done without any assumptions on how the table is ordered. For example:
I have attached the data in an excel file here. I'd like to select (or highlight) the rows (based on names) in the table on sheet1 based on the list provided in sheet 2. I've been struggling with using a formula for this one. The list on sheet 2 may be longer than the table on sheet 1(since there are entries on the list which might not be present in the table column 2), and the reverse may also be true. I would like to select "entire rows" of names in the table that are there on the list on sheet 2, not just the names column in the table. See the desired result on sheet 3.
I have a table in columns C:J, starting in row 7. I have a macro that selects and copies the whole table (after I filter the table) and pastes to another sheet. Recently I've realized that I can only copy/paste C:I or it will mess up my formulas during the process.
This is the code I have right now, which works beautifully for the whole table:
What I need is to know how to copy everything in the table except column J. I can't just copy a certain range cells because the number of rows is variable. There has to be a way to modify the ActiveSheet.ListObjects("Table36") right?
I have a data input sheet on a spreadsheet with a pick list where we can select an option, but I need another 'flat file friendly' option to appear on a second sheet where the data will be pulled from. For example
If on the front sheet Flat Roof Insulation is selected, it needs to return FRI into the other sheet, there are 45 options in the list.
Basically I have been presented with a table (which can't change) and isn't well formatted shown at bottom of page.
Ideally I want to like extract and cross analyise some of the elements in the table to load into a database. Due to the formatting I cannot find a way of doing this which identifies and picks out the relevant bits.
For example; how would I gather a way of identifying what the districts is, what the road type is (i.e motorway), whether it's rural or urban, and the totals for a number cross referenced factors (i.e total road lengths for Dacorum) based on the existing formatting?
I have tried indexing, vlookups, index, pivot tables, index match match ...all to varying degrees of success. Whilst these function to a degree if the table orders were to be formatted differently in the future these methods cease to work and I would have to check this manually.
Therefore if you can alert me to any possible way of picking factors based on criteria and then returning the results.
Example table (it has a number of headings and in a variety of positions within the page)...
Motorway and Trunk Roads Principal Roads (De-Trunked) (Route Length)
is it possible to select the data to view by running a macro. my table is based on dates. i would like to see all data before the date i run the table and 6 weeks in front only.
i have a pivot table that shows the customer names. when i select the customer drop down box i can all the customer i want to see. but in the table itself there are some customer not showing. this is the first time this has happened
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.
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
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
I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.
I have a workbook that I am continually adding and removing pages from. The pages are used together in groups of 3 and as a result are named 1.1,1.2,1.3; 2.1,2.2,2.3 and so on. I have a macro that works on sheet 1.3 and now need it to repeat on sheets 2.3, 3.3 etc depending on how many sheets I have open and active in the workbook. Is there anyway that I can create a macro that will work on all sheets that have titles ending in 3 and then end if there are no more? Thus it would work if my workbook goes to either 2.3 or to 7.3 and so on.
I am currently working on a simple macro to help with formatting a spreadsheet. The number of tabs on the spreadsheet will change to reflect the number of companies I'm running reports for. But the same process is to be applied to each tab. The issue I'm facing is I don't know the appropriate code to select all tabs without specifically referencing them.
The code that I would like to apply to all tabs is as follows: ActiveSheet.PageSetup.PrintArea = "$A$1:$J$85" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = ""
This should be so simple (I would have thought), but so far I'm struggling.
Using Range / Offset or something similar, I want my macro to select a certain cell (which is dependent on the data in another cell) on my worksheet so that the user can then input data into the chosen cell.
Something along the lines of:
Range("A1").Offset(23 + t4, 1).Select
[where cell t4 contains a number which dictates how many lines down I want it to jump]
The purpose of the excel sheet is hour administration. In the first page you fill in which group it is about, what week it is about and the amount of hours to change.
There are several other sheets named after the group that are filled with some kind of agenda.
An example: I want to make a change to the amount of hours group X will be there in week 3. I fill out the 3 columns (Group, Week, Hours) and hit a button. It should now check those 3 columns for data and modify the hours for the correct group. It is not done yet but I'm running into a problem already, the function I use to find the row with the correct weeknumber in the Group sheet returns wrong numbers and I don't know why.
VB: Private Sub CommandButton2_Click()
Dim Week As Integer Dim WeekRow As Long Dim Groep As String Dim Uur As Integer
[Code] ....
What I am expecting to happen is this: It checks A1, B1 and C1, selects the correct sheet, executes the function FindRowByValue (the week number is in column B) and fills Week Row with the result, then returns to the original sheet, gives a debug message and repeats this 5 times.
The debug message shows that the Week Row is not returning the row with the week number I filled in but instead returns either the week number itself (so if I wanted to find week 5 it returns a 5, while week 5 is in row 28), or a different number that is incorrect.
I have a comparison macro that compares one sheet with the next, it is called sub comp1 (). There is a condition whereby columns must be removed before sub comp1 can run. To remove these columns I run the macro called sub colcut (). What I need is the following action:
If cell BJ4 of current worksheet = "RE COMMENT" then run sub colcut () followed by sub comp1 () otherwise only run sub comp1 (). FYI The macro is to be run manually via a button rather than triggered by the value of the cell.
I run a macro on a spreadheet in which the rows increase daily. In the macro I simply recorded an end.down to select a column of data which ends up being fixed row numbers in the code so the next time I run the macro it only selects the range I selected last time.
I have spreadsheet with 13 sheets the first of which is a title page, what I wish to do is hide the other twelve sheets and somehow be able to select them from a drop down box (or similar what ever works with a macro)on the front page, the selected sheets would then appear unhidden.
I am OK with formulas etc but macros are beyond my ability even trying to follow examples & tutorials online has me lost.
The sheets all have specific but different names and none need to be selected more than once (although if possible it would be good). I understand where to put the macro in but as to how it is written ......
