Macro Selecting Cells Via Variable?
Feb 18, 2012
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.
View 3 Replies
ADVERTISEMENT
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?
View 4 Replies
View Related
Jul 9, 2013
I have to select and cut a column with on several lines empty cells. I use the following code
'Range("c2", Range("c2").End(xlDown)).Cut' but this code select only the data till the first empty cell.
How can I select the whole column till the last cell. The column B is filled with data on every row so I think that I have to use the offset code.
View 2 Replies
View Related
Feb 19, 2014
I have some reports that I run that go out to analyst daily and I use this script to merge all the documents together. They are the same everytime. However it includes a series of blank rows because the vba I use to create them I believe causes this. Is there a command to remove the VBA when merging them together. Here is the selection copy piece.
HTML Code:
'Import a sheet from found files
Do While Len(fName) > 0
If fName ThisWorkbook.Name Then
[Code].....
View 3 Replies
View Related
Feb 21, 2009
I want to be able to select a cell, for example A10, this would then run a macro that would copy and paste several cells of information along that row and paste it on another worksheet. However, if i then choose a15, a3, a79 in turn (using the control key), it would run the macro using those rows in turn.
i'm looking forward to hearing if and particuliar how this could be achieved.
View 13 Replies
View Related
Apr 3, 2009
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.
View 2 Replies
View Related
Apr 4, 2013
I want to copy a range where the row is variable (i in the Loop I'm doing) but the columns stay the same. What am I doing wrong?
Workbooks("Labor Info").Sheets("Transfer").Range("A" & i & ":E" & i).Select
View 1 Replies
View Related
Feb 26, 2009
I need to select starting with:.
Columns A-O
Rows 1 - ( LR of ColB - Col A is empty )
View 9 Replies
View Related
Aug 28, 2009
I require vba code which will allow me to select a range of variable size.
Starting from cell A2 use :
Range(Selection, Selection.End(xlDown)).Select
However then using the line below may not select the entire range as some columns may be empty
Range(Selection, Selection.End(xlToRight)).Select
View 9 Replies
View Related
Jun 3, 2006
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.
View 3 Replies
View Related
Jun 21, 2006
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....................
View 4 Replies
View Related
Aug 22, 2014
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.
View 7 Replies
View Related
Mar 19, 2012
I have below code which I was thinking is very simple to select variable worksheets. However I get Debug error when running:
Code:
Dim rng As Range
Dim cl
Set rng = Sheets("Budgetted sku's").Range("A2:A24")
For Each cl In rng
Sheets(cl.Value).Select
Next cl
View 5 Replies
View Related
Jan 21, 2010
I need to tweaking some code. I currently have the following
View 2 Replies
View Related
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
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
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
Mar 12, 2009
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.
View 7 Replies
View Related
Mar 15, 2008
To make things simple, I have a range of A1:A10. Within this range I only need to select cells thats contain "Tax".
View 9 Replies
View Related
Jan 20, 2009
I have 2 Cells
Cells( 2,1) and Cells ( i , 1) with "i" a While Counter, and i need to Select All the Cells between that, including both cells.
I'm tring Cells(2,1):Cells(i,1) but obviously this didn't work
View 9 Replies
View Related
Sep 5, 2008
A macro that only selects cells which are coloured light green. I need this so that I can add it to an existing macro that I've written to remove conditional formatting from these cells only.
View 14 Replies
View Related
Jan 31, 2009
I am trying to select a range of cells. The range I need to select starts in Cell A1. I need to select every row with data in column 1 up to the first row with no data. I then need to expand the range to all columns up the last column with data, including columns with blank cells.
The problem with my code relates to the selection of columns. It is only selecting up to column F. Column G is empty, Column H has data, Columns I-J are empty, Columns K-R have data.
View 5 Replies
View Related
Sep 15, 2009
when run a macro to make the macro select all the cells in a specific column that only contain data i.e. i have a spreadsheet 5 columns wide by 23 rows deep, the macro at the moment selects the 5 columns (this never changes) but the number of rows always changes 123, 56, 1, etc i need to to always select the rows that have data.
View 14 Replies
View Related
Mar 21, 2013
I found the code below which randomly selects number and places them into column "I". However, I need the code to search through column "F" and randomly select numbers from column "F" and place the result in column "I".
' Select the indicated number of items from the
' currently selected cells.
Public Sub SelectRandom(ByVal num_to_select As Integer)
Dim num_items As Integer
Dim indexes() As Integer
[Code] ........
View 3 Replies
View Related
Jun 5, 2013
I use this code to select highlighted cells within a column
Code:
For Each CELL In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If CELL.Interior.ColorIndex xlNone Then
CELL.Select
End If
Next
the thing is that it is working from bottom to top, is there a way to make it work from top to bottom?
View 5 Replies
View Related
Oct 10, 2013
how do I select all cells from say B6 to the one above the active cell, and all the cells below the active cell, ie i want to select all cells in a column except the active cell and the first 5 cells. (in VBA of course).
View 8 Replies
View Related
Jan 9, 2007
someone the VBA code which only selects visible cells in a sheet.
i.e. if for example, range("a1:d5") contained data BUT
range("a6:d100") contained formulae but NO data
the macro would only highlight range("a1:d5")
However, when say range("a6:d10") contained data, then the macro would highlight range("a1:d10")
and so on.
I want the mcro to do this without me having to manually change the ranges when running the macro.
View 9 Replies
View Related
Jun 11, 2008
How do I use SpeciaCells to select cells in a range with a certain value?
In the same manner that you can use SpecialCells to select blank cells and then delete that entire row I want to do the same for a cell containing a specific value/text
I have been using SpecialCells to assign a value in blank cells as a temporary marker. I then need to delete the rows with the temporary markers. I have not been able to use SpecialCells to select the temporary markers .
View 9 Replies
View Related
Oct 12, 2006
I have two excel sheets with lots of data in, that i import from a database. Based on these two sheets I make four other sheets that are suppose to get input from this two sheet. To decide what lines I shall copy to the other sheets, I use a filter. But my problem is that there can occur new data too the two main sheet....
View 6 Replies
View Related
Mar 10, 2014
Using VBA, I need to Select A1:C14.
The problem is that A1:C14 contains blank cells, and there is also an adjacent column D that I do not want to copy.
So, UsedRegion and CurrentRegion aren't doing it for me. (It selects Column D too.)
Obviously, this is an example...the real data set is an export and varies in size.
View 1 Replies
View Related
