I want to write VBA code to select a cell in a row if one of the cells in that row contains a pre-define text. e.g. In cell A15 the text is Blackbird and A25 the text is Redbird. Sometimes this changes and A33 contains the text Blackbird and A19 the text Redbird. I want a VBA code that will select the cell in column E in the row that contains the text Blackbird. I must be able to specify the text and the column.
View 3 Replies
i am trying to figure out a vba code to select the last cell in column C that contains "Cgy".
View 3 Replies View RelatedIs there a way to search the header of a file or specific row for a specific string of text, then select the entire column below that string?
View 5 Replies View RelatedI would like to search Column C for an instance of the text "Std. Residual", then cycle through the non blank cells to the right and run some formatting code:
VB:
'this line will be modified so that i cycle through the non blank cells that i have found
For row_cycle = 1 To 7
'not signifcant
If Abs(ActiveCell) < 1.96 Then
Selection.Interior.Color = 255
[Code] .....
I then want to search for the next instance of "Std. Residual" in Column C, do the same again, and so on for the entire Worksheet.
I have a text box that has default text in it. When I mouse down on the box I would like it to select all of the text in the box automatically. Does anyone have any code to do this?
View 3 Replies View RelatedI'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
[Code].....
I have 02 separate excel workbooks
01 = source file
02 = data list
The workbook "source file" contains all the data
The workbook "data list" contains data for work with Drop-down list. And i can easily select my required description by just dropping down the list.
In case, i have new description i add it in the source file and it is updated in the working "data list" sheet.
This applies to Field device column also. But i want to improve the selection criteria by creating such script or formula which could check the input of the description and automatically selects the field device and its relevant signal (output type).
Screen shot is attached : Automatic Selection.jpg
I am trying to find a macro that look for last non empty cell in column A and them paste a formula/comment in all cells of column B.
View 3 Replies View RelatedI know that this could be a stupid question, but was wondering if it could be possible. I would like to select a column by a keyboard shortcut. If I would press a key combination adding the letter of a column as example F, it would select the whole column. I was trying to search for this in google, but no luck.
View 10 Replies View RelatedI'm trying to make the below two column selections into a selected range to clear out formulas/values in the range.
Current code segments:
ActiveCell.EntireColumn.Select
Columns(ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column).EntireColumn.Select
Is there a way to combine the two code segments into one selected range?
In the attachment is a file, which allows me to specific a Column and hide. For Example enter "H" in the InputBox and Submit the Column "H" is hide.
What do i have to change in the code if i want to hide the "H" and the Column two columns next to "H". In this case "J".
HideColumn.xlsm
I have a sheet that begining in A3 and going down need to look for the First Instance of the text String "Loan Documents" and down to the last instance and select the corresponding range in column B.
Example: if the Text String appears in Range(A14:A32) I need the Range(B14:B32) to be my selected range.
I have four cells that contain text. All have connected check boxes with TRUE FALSE.
I need to be able to select anyone one of these cells with a check box, and have it's text appear in one separate cell eg: A1.
I have no issue connecting check boxes etc. I have no issue reproducing the text from any of these cells into multiple cells with a check box. But they have to be selectable and reproducing in one cell only (eg"A1").
I am wanting to use VBA to select a text box that is in, say Range D65:AD65. I will then delete the text box. I imagine this is simple, but I know very little VBA. I tried numerous searches on things like text box, shape, and shaperange, but could not find the solution. My range is constantly changing so I do not want a specified range in the code. I already have code ready to have the range selected, just can't select the text box in it.
View 9 Replies View RelatedDoes anyone know of a way to select fragments of the following text?
The string is "toby.cameron1".
I need to be able to select just the "toby" and "cameron" parts.
Also this string is variable in that it might be "james.brown" or similar without the number at the end.
I can't use a Left or Right statement because of the varying length of names and the uncertainty of having a number at the end.
Is there a way I can copy the start of the string until the period. And do the same from the end but ignore the number whether it's present or not.
From that, is there anyway to copy the numbers off the end of a string?
i.e. my string is ASY-623 or DRGN-12 or WDSR-7
The string will always be letters a dash and then numbers.
How do I copy only the numbers?
I have files where empty textboxes have been copied over tens if not hundreds of times, thereby slowing down the scrolling speed immensely.
I would be interested if there was a macro to
a) select all empty text boxes
b) select and delete all empty text boxes
I am working with a spreadsheet that has asterisks in place of text that should be added. The problem is that it is surrounded by HTML code. I need to be able to select the cell, go to, and highlight the text, where I can hit delete, or just start typing to replace the asterisks, BUT NO OTHER TEXT IN THAT BOX CAN BE EDITED.
Attached is an example of what I'm wanting to replace.
example 1.jpg
I want to create a macro that will go to the text I have highlighted here, and STOP just how this is, so I can simply put what I want in place of the asterisks, and move to the next cell.
Is this possible?
I have a range of data that is sorted (about 20,000 rows). In about 15,000 of these rows column D will contain a generic text string. How do I select the entire row where the generic text string appears in column D?
View 3 Replies View RelatedHow can I select a whole row based on the text value of a cell.
View 4 Replies View RelatedI am having difficulty with creating an IF formula that will only show 9 digit numbers. If the cell the formula is looking at has less or more than a 9 digit number in it, or the cell ha no value, the result will be blank.
View 6 Replies View Relatedi am trying to select column A and column B and apply a filter. however, I don't want the filter to be applied to row 1...
View 2 Replies View RelatedI am trying to select a reference cell value from a column that is LESS THAN.
1. My reference cell value is 22.
2. I look at first row and since 22 < 30, I want to pick values in column 3 (30)
3. Question, how would I get the output below my reference output 22, like i have below?
I want the entire row and column of the active cell to be selected - now only column "A:A" gets selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveCell.EntireRow.Select
ActiveCell.EntireColumn.Select
End Sub
how can I just select every other (nth) column based on a selection?
So say I have data in columns A-G. I want to select the data block from A1-G100 and then run this macro so that it keeps just every other (or nth) column selected. So the process would be to highlight the data block, then run the macro and I would be left with the data block every other (nth) column selected so I could format all those cells the same way (column width adjustment so I can't use conditional formatting with =mod(),2).
To take it to another slightly more complicated step- I'd like to ultimately include a box that pops up and asks for how many columns I'd like in between.
So that I could select a data block, then hit a button for the macro, type in a 3 to indicate I want to select every 3rd column and then hit go, and then be left with excel having every 3rd column selected.
I'm sure you're all familiar with Text Boxes:
You can add shapes and text boxes (text box: A movable, resizable container for text or graphics. Use text boxes to position several blocks of text on a page or to give text a different orientation from other text in the document.) to a worksheet by using the Drawing toolbar.
Did you know that if you click the text box button, then click on a spreadsheet (inserting a blank text box), then click off the text box, a small blank text box will remain hidden from view on your spreadsheet? Once hidden, the only way to find it is to slowly move your cursor over the Text Box edge, and watch the cursor momentarily change shape. Did you also know that if you copy or fill down the cells behind that text box, it will make as many copies of itself as you made for the cell? Not too dis-similar from a virus.
Imagine what would happen if you unknowingly had some of these text boxes on your main calculation sheet (200 columns by 2000 rows); one where you regularly copied formulae/cells for a period of over four years. Yes, (judging by their names e.g. "Text Box 29413") the thriving population of these invisible beasties is now in the neighbourhood of thirty thousand! The damn things are worse than fleas; and they're impairing the function of the whole workbook.
Help! Would someone please provide a VBA macro to find, select and delete these little devils from the spreadsheet (just this one spreadsheet)?
I am using EXCEL VBA and SQL statement to try to select some data in worksheet
Below is my worksheet sample
datee name product_name product_code
2014/6/1 Eric Toy_A C0001
2014/6/3 Stephen Toy_B C0003
2014/6/16 Calvin Toy_C C0003
2014/6/19 Perry Toy_D C0001
2014/6/23 Calvin Toy_E C0002
Here is my code segment for select data
strQuery = "SELECT * FROM[List$] where promoter_name = 'Calvin' and promoter_code = 'C0003'"
Set rs = cn.Execute(strQuery)
This is success to select data with no error
============================
But when I changed the condition to use datee column
strQuery = "SELECT * FROM[List$] where promoter_name = 'Calvin' and datee = '2014/6/23'"
It cannot work The error message said data type incorrect
Do I need to set any thing before run the sql?
The first code is on the top of the module.
Code:
Private Const OPEN_TIME_COL = 3
Private Const CLOSE_TIME_COL = 4
Code:
RowNum = .Cells(.Rows.Count, CLOSE_TIME_COL).End(xlUp).Row + 1
.Cells(RowNum, CLOSE_TIME_COL).Value = Now
So according to the second code, it records the closing time(now), in the CLOSE_TIME_COL(Which is D)
in the next availabe cell, from the CLOSE_TIME_COL....
But here is the part i am trying to change
Code:
RowNum = .Cells(.Rows.Count, CLOSE_TIME_COL).End(xlUp).Row + 1
So that, what it should do is find the last cell used in column C, (OPEN_TIME_COL) and shift right.
if the last cell used in C is C1, CLOSE_TIME.Value should be in D1
for example somthing like this... but this code doesnt work
Code:
RowNum = .Cells(.Rows.Count, OPEN_TIME_COL).End(xlUp).column + 1
First although it returns the correct column, it always returns Row 1. Beyond me! Secondly I could really use some help in a line or two of code to select the cell at the intersection of the column and row.
Sub test()
Dim name As String
Dim number As String
Dim col As Integer
Dim row As Integer
Dim RowNumber As Integer
Dim ColNumber As Integer
Dim rng As Range
ActiveWorkbook.Sheets("Sheet1").Activate
name = InputBox("type a name from Column A")...........
As the title sugests I would like to Select a cell and a macro will highlight the row and column
Sub Select_Entire_Row()
Dim RowNo As Integer
Dim ColNo As Integer
RowNo = Selection.Row
ColNo = Selection.Column
If RowNo.Value >= 1 Then
Cells(RowNo, ColNo).EntireRow.Select ' I want it to do both not one then the other
Cells(RowNo, ColNo).EntireColumn.Select
End If
End Sub
I would like to select the last few cells in a column, however the number of rows is not fixed, it changes. This can only select the fifth last cell:
Sheets("ABC").Range( 'A1:A150').End(xlDown).Offset(-5, 0).Select
Ultimately, i want to select the 5 cells from the fifth last to the last:
from this: Sheets("ABC").Range( 'A1:A150').End(xlDown).Offset(-5, 0).Select
to this: Sheets("ABC").Range( 'A1:A150').End(xlDown).Offset(0, 0).Select
If possible, with the cells selected, can i do something similar to this:
Range("B62:B67").Select
Selection.AutoFill Destination:=Range("B62:B114"), Type:=xlFillDefault
Range("B62:B114").Select
The underlined parts is the part which make me headache. How do i solve this?
