VBA If Statement - Select Active Cell
Oct 14, 2013
If statement, any way where I can, when I run the macro, choose active cell?
Ex.
Start macro
and it will ask for active cell,
check if active cell higher then 0
then set cell to color red
I have this atm:
Code:
Sub LookForValue()
' Declare Current as a worksheet object variable.
Dim Current As Worksheet
' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
[Code] .........
View 8 Replies
ADVERTISEMENT
Jul 7, 2008
I want to put a range select statement to select a cell and count down 10 cells and copy.
View 9 Replies
View Related
Feb 5, 2007
I need to select the row for the active cell without using an explicit RC address. I don't seem to be able to make the syntax work.
View 2 Replies
View Related
Mar 30, 2014
I am trying to select a shape in a cell and place a cell reference into the shape. I know the cell address, but do not know the shape reference. (The row that the cell and shape reside came from copying the row from another sheet and inserting it into the current sheet). I can not seem to come up with the correct code to accomplish this. Right now I have:
Cells(r + 1, 48).Select
ActiveCell.Shapes.Select
ActiveShapes.Formula = "$AW:" & r + 1
Where r+1 is the row where the line finally resides after insertion and "AW" is the column to the right of the cell where the shape resides.
I have tried to put the cell reference into the shape at the location where it is copied from but the reference is not dynamic and I can not seem to get it to be. Another issue that will arise is that the rows in the new sheet will probably be sorted and I question if the cell reference will follow the shape's new location.
View 7 Replies
View Related
Apr 23, 2014
I want a simple macro which will go to the cell in row one in the active cell's column
View 2 Replies
View Related
Aug 21, 2009
Using vba how do I tell a macro to select the row that the active cell is in?
I'm just using a basic delete Row macro but I'd like for the macro to automatically select the entire row when it's time to delete instead of me highlighting the section.
View 2 Replies
View Related
Jun 10, 2014
What is the difference between the two ?
When should I use each one ?
View 4 Replies
View Related
Oct 21, 2009
I want my code to evaluate each cell in column B, and based on its value, copy the row from D to X and paste on the newly activated worksheet. I'm trying to use Offset, but it's not working.
View 6 Replies
View Related
Oct 27, 2009
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result.
For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z).
The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View 4 Replies
View Related
Sep 22, 2007
A custom UserForm pops up when a cell is double-clicked. The form contains a ListBox that presents the user with a list of values to select. When the UserForm first pops up I want the item that matches the value of the activecell to be highlighted/selected.
View 8 Replies
View Related
Mar 23, 2009
I RECORDED THIS MACRO BUT I WILL LIKE TO MAKE TO AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS MY CODE
Sub FORMAT_AS_A_TABLE()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$1900"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleDark5"
End Sub
View 3 Replies
View Related
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
Oct 4, 2007
I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.
Private Sub Worksheet_Activate()
If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then
Me.ClearPrevious.Visible = True
Else
Me.ClearPrevious.Visible = False
End If
Sheets("MASTER").Select
End Sub
View 9 Replies
View Related
Sep 11, 2013
I am putting together a procedure to calculate a value based on all of the active cells in column "A" and a couple other conditions. "k" is my variable to put a value into (k,8) until all the active cells in column A have a corresponding value in (k,8)
I have my scenarios all built out into If statements and the code runs....but no value is being added to cell (k,8) for any of the rows with entries in column "A" when the procedure is finished running. Depending on the values in (k,2) and (k,3) there are four different ways the value in (k,8) needs to calculate as I have built out below. Why my values aren't being added to (k,8)?
Code:
Sub CalcSpecialFee()
Dim k As Integer
Dim cell As Range
k = 4
Application.ScreenUpdating = False
Sheets("Summary").Select
[Code] .........
View 1 Replies
View Related
Jul 17, 2014
I am trying to select the active row and the next 21 rows down, so in all 22 rows should be selected after the macro runs.
View 4 Replies
View Related
Jan 20, 2010
I've manage to do this a while ago, unffortunaly my hard drive got damaged.
View 3 Replies
View Related
Oct 8, 2013
I need a macro that will select row 15 in the active column. (basically returns to the top of the data where rows 1-14 are frozen in place, only in the current column).
I have been able to accomplish the opposite (skip to specific column while maintaining active row) by using the code below:
Range("V" & (ActiveCell.Row)).Select
But it does not work when I try the reverse:
Range((ActiveCell.Column) & "15").Select
View 3 Replies
View Related
Jul 24, 2014
I have the following code Range("M402").Select
This very simple code brings me to M402 everytime I click on the button.
However, I would like to stay in the same column that I am at the moment when I click the button, and go to row 402. (i.e. if I am in cell "Z56", I would like to be redirected to "Z402" when I click the button.
View 3 Replies
View Related
Mar 25, 2009
I have multiple excel workbooks open at the same time. I need to run a macro on one of them every 15 min but I need to return to the active window when the macro is done. How do I read what window is active then return to it when the macro is done?
Sub AutoSave()
dTime = Now + TimeValue("00:15:00")
Application .OnTime dTime, "AutoSave"
Windows("data.xlsm").Activate
Sheets("Data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="c:excel" & Format(Time, "hhmmss"), FileFormat:=xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows(1).ActivatePrevious
End Sub
View 2 Replies
View Related
Apr 19, 2014
So for example if I have a1, a5, and a6 selected, I want to be able to select row 1, 6, and 7 in one action. Is there a hotkey for this, and if not what macro could I use?
ActiveCell.EntireRow.Select only gives me one row. I want to be able to select all of them at once.
View 1 Replies
View Related
Oct 30, 2012
I have data that i import on a daily basis, the data can range from a couple of rows to thousands,
What I need to do using VBA is select the active cells, where cells in row ''G'' is blank then delete the entire row where that cell is.
View 3 Replies
View Related
Dec 10, 2012
whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.
This action should be restricted to one sheet in the workbook.
It's for Excel 2003.
View 3 Replies
View Related
Jun 8, 2014
I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.
The code I am using is:
[Code] .....
The code that is not working and bringing up an error is:
[Code] .....
Attached File : Copy of Testexample.xlsm‎
View 2 Replies
View Related
Feb 21, 2007
I have a form with seven check boxes on it. The code that I have been working on is below. Using the select case works exactly as I want it to unless someone checks the combination of boxes that returns a "True" for both "Apples" and "Oranges". If that combination is selected I cannot get the select case to return the correct form. I was trying stay with the select case code but I am not sure that this is the correct approach for what I am trying to accomplish.
Private Sub CommandButton1_Click()
If Form1.CheckBox1.Value = True Then X = "Apple"
If Form1.CheckBox2.Value = True Then X = "Apple"
If Form1.CheckBox3.Value = True Then X = "Apple"
If Form1.CheckBox4.Value = True Then X = "Orange"
If Form1.CheckBox5.Value Then X = "Orange"
If Form1.CheckBox6.Value = True Then X = "Orange"
If Form1.CheckBox7.Value = True Then X = "Orange"
Select Case X..........
View 2 Replies
View Related
Nov 8, 2007
I'm trying to put the following into a workable VB code. I don't know whether it is best to use Select Case or If Then statements or whether either can perform the tasks.
Case 1 - Range(“N1”).Value = “KG” Then Range(“M1”).Value = Range(“V1”).Value
Case 2 - Range(“N1”).Value = “M2” Then Range(“M1”).Value = Range(“V1”).Value
Case 3 - Range(“N1”).Value = “NO” Then Range(“M1”).Value = Range(“U1”).Value
View 9 Replies
View Related
Jul 21, 2008
I have this select statement:
Dim ResourceMonth As String
Dim mActiveSheet As String
ResourceMonth = Sheets("Finanace").Range("J2")
Select Case ResourceMonth
Case "Jan"
Sheets("Jan").Select
Cells(8, 4).Select
z = ActiveCell.Row
SSRR = ActiveCell.Value
mActiveSheet = "Jan"
Sheets(mActiveSheet).Range("[test1julycheckbook.xls]Jan!MaxHoursJan") = (Sheets("Workdays").Range("F2")) * 8
View 9 Replies
View Related
Feb 26, 2014
I am trying to select a group of cells in an inactive worksheet from code in a module but I keep getting a run-time error 9 message and I cant seem to find the syntax problem.
I pasted my code below and the error occurs in the last line before the end sub statement.
[Code].....
View 12 Replies
View Related
Nov 10, 2009
I could use some assistance in creating a macro that will delete all and only text boxes on the active sheet. Some text boxes will be empty, but I still wish for them all to be deleted. Through searching the forums I see a similiar code, but it deletes all shapes on the active sheet:
View 8 Replies
View Related
Apr 16, 2009
when i write the following line to open the recordset then it gives me syntex error.
rs.Open "select batchno from tblmain", cn, adOpenKeyset, adLockOptimistic, adCmdTable
and if i write the following line then it works fine.
rs.Open "tblmain", cn, adOpenKeyset, adLockOptimistic, adCmdTable
View 8 Replies
View Related
Apr 13, 2007
I get an application defined or object defined error at the select statement 1004...I know I know I shouldn't be selecting in code so I'll fix that but why can't I set the range?
NumRows = Sheets("Timelines").Rows.Count
LastColumn = Sheets("Timelines").Range("A1").End(xlToRight).Column
LastRow = Sheets("Timelines").Range("A" & NumRows).End(xlUp).Row
Sheets("Timelines").Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
TIA, Charlie Brown.
View 7 Replies
View Related