Vba - Is There Some Kind Of Loop Command For This

Apr 2, 2008

I have a spreadsheet that has a lay out like below

A A A
* A A
* A A
B B B
* B B
* B B

and so on where every letter is a new cell and * = empty cell

I need a peice of code that will highlight all of section A. Section A could have 1 row or an infinate number of rows.

What I need is like a peice of code that means that it will start selecting rows starting from "A3" which is the 1st cell with data and stop selecting rows when the next row has data in the A column.

View 9 Replies


ADVERTISEMENT

Put A Range Command As Loop?

Aug 14, 2014

how do i put the following as loop. Range("A2) then Range("A4") then Range ("A6) and so on.

View 8 Replies View Related

Loop Command Until Blank Row

Mar 7, 2007

to get the code myself but nearly smashed the computer , then been searching for hours for the answer and still no luck, so last resort, to what I bet is an easy solution.

I'm building a spreadsheet and want to create a macro that will copy a row to another spreadsheet, and continue repeating this until it reaches a blank row and then stops the macro. For example:-

Row 1 contains Anna
Row 2 contains Ben
Row 3 contains Clive
Row 4 contains nothing

I want excel to copy rows 1, 2 and 3 to another workbook but once it reaches row 4 it realises it's reached the end of the list. (The list won't always be three rows but will be continous)

View 14 Replies View Related

Loop Through Command Buttons

Sep 9, 2009

I have a multiple of command buttons that I want to make visible. I have the following code which I am trying to get to work. Maybe I am barking up the wrong tree.

View 2 Replies View Related

VBA To Know What Kind Of Border It Is

Mar 28, 2009

Does any know of excel code to know what know what kind of border it is. I converted a pdf file and now the data are on excel and there are borders and it looks to me like it is border with thickness of medium but when I run a code I produced and mark X to the column next to it to show that it is that border it gave me X on all the rows and so the medium thick border is excel grid without any borders.

View 9 Replies View Related

Command Button In For Loop Cycle

Nov 18, 2012

There are 2 buttons on my worksheet below each other. Each button has a size of a range (i.e. "G33:G34", "G36:G37").

This is what I would like to do:
1. Click on the first button
2. Copy A33:F42 and insert them below row 33
3. Move the buttons to the same position (10 rows down) on the sheet
4. Copy the existing buttons and put them in their original space (i.e. "G33:G34", "G36:G37")
5. Do it as many times as I click on the first button

I have this, and I have no idea to the remaining part...

Private Sub CommandButton1_Click()
Range("A33:F42").Select
Selection.Copy
Rows("33:33").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Range("B33:B42").Select
End Sub

View 4 Replies View Related

VBA Loop For Making Command Buttons Visible

Jun 10, 2013

I'm trying to write a code to make a number of buttons visible depending on a cell value

I have 10 command buttons all are invisible and I want to show only the first x
x is the value of cell "A1" in "Sheet1" (will be from 1 to 10)

Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Long
For i = 1 To Sheet1.Range("A1").Value
CommandButton& i.Visible = True
Next i
End Sub

View 1 Replies View Related

Loop Command Across Multiple Rows And Worksheets

Jul 23, 2014

I have a large data file with several different worksheets. In one cell I have a large set of individual numbers (i.e in the cell there is "123456 234234 939829") This value is my 'sample' number. The same sample could be in multiple rows on one sheet and could also be on other sheets. I am trying to find the sample in a column (on some sheets its column "Q", other sheets it could be different columns, but that column is fixed per sheet) and then copy the information on the same row in columns A & B and copy it to my target sheet. Then the next place the sample appears copy that info to the next row of my target sheet. So far I am stuck getting it to find the multiple values on one sheet. My code is below.

Code:

Sub Samplesearch()
Dim sample As String
Dim x As Integer
Dim y As Integer
Dim lr As Long
Dim lsr As Long

[Code]....

View 5 Replies View Related

Loop Command Macro To Copy Data Between Worksheets

Aug 11, 2009

I am having trouble creating a macro for an excel file I am working on. I do not have much experience writing macros, so I apologize for any confusion. I have created a simplified version of the file I am attempting to write a macro for and attached it to my post here.

At this point it would be helpful for you to open up the attached file so that you can understand my explanation.

Basically what I want the macro to do is:

1. Match up "Product" on Wksht(Input) with "Product" on Wksht(Data).

2. Copy the "Usage per ton" and "Dollars per ton" values corresponding to the given product from Wksht(Input) to Wksht(Data) for the correct month. (The user will manually enter the month, ie "August 09" into Wksht(Input) each month when he runs the macro.)
++The color fills will not exist on my finished macro. I only put them there to illustrate the values that will be copied and pasted.

I want the macro to be written with Loop commands so that it will check to be sure the product number matches with the given location for pasting the data values. I should also note I use PasteSpecial_Values because of the formulas in the cells on Wksht(Input).

View 12 Replies View Related

Loop To Increment Row Number And Execute Command Where Set The Formula

Oct 11, 2011

Make a loop where I can increment the row number and execute the command where I set the formula?

View 1 Replies View Related

Use Drop-down List To Choose What Kind Of Calculation To Apply To Certain Cells

May 26, 2014

I have a drop down list that shows phrases for different math functions, like "divide by 2," "divide by 4," divide by 8," etc. When one of these items is selected, I want it to perform that specific calculation on a range of nearby cells.

However, I also want the user to be able to see the original value of the cells if they select "show original value" from the drop down list.

I have attached a workbook as an example. I think macro would probably work best.

oz_list_math.xlsx

View 5 Replies View Related

Populate A Specific Range Of Cells Based On A Value Of Any Kind In A Header Cell

Sep 22, 2009

I need to populated a specific range of cells based on a value of any kind in a header cell. Attached is a sample of the end results. Please remember each header cells needs to determine what happens the range of cells below the header cells.

View 3 Replies View Related

VBA Command Loop Through Spreadsheet Rows Until Blank Rows

Apr 22, 2012

Using excel's text to speech I've put together a basic spreadsheet.

[URL]

Code:
Function talkit(Speech)
Application.Speech.speak (Speech)
talkit = Speech

[Code]....

View 9 Replies View Related

A Different Kind Of "missing" Detection

Mar 12, 2009

Background: I have 3 lists. List 1 is a column of unique identifiers. List 2 is a column of unique identifiers. List three is supposed to contain as many rows as the number of rows in List 1 * the number of rows in List 2. Each row in List three has as its unique identifier a value from List 1 concatenated with a value from List 2.

What my problem is: I want to determine, when looking at each row in list 1, if there is an corresponding entry in list 3 for every row in list 2.

View 10 Replies View Related

Press Command But And Execute The Code Assigned To Another Command Button

Apr 14, 2009

Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).

View 9 Replies View Related

Shell - Adding Another Command To Forfiles Command?

Apr 19, 2014

I am playing around with the Forfiles command (being called from Excel via Shell)..

I can't for the life of me get it apply a second command (such as getting the file size of each file)..

For example:

VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d

[Code]....

The end result being the filename and the filesize shown..

View 4 Replies View Related

Excel 2007 :: Using Search Command With IF Command

Jun 20, 2013

Excel 2007

I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")

Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned

how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.

View 2 Replies View Related

Loop Within A Loop (repeat The Loop Values)

Mar 31, 2008

With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).

I have a named range called 'territories' that contains the list of X101 -> X152.

I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!

View 9 Replies View Related

Loop Through Multiple Files And Call Macros (but Unable To Loop)

May 14, 2014

Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.

Why it stops after one file when using "Application.Run..." to call the macros?

NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.

Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long

[Code]....

View 4 Replies View Related

Store Row Number Within Loop And Delete All Stored Rows After Loop?

Sep 11, 2013

I have working code that returns a row number within a for loop based on parameters I set.

Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.

Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*

Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.

All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.

View 4 Replies View Related

Loop Through Cells And Ranges Reverse Order With Backwards Loop

Aug 30, 2006

I am looping through each cell in a range and I would like to loop in reverse order.

Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next

I have tried:

For Each CELL In Range("C" & TotalRows, "C1")

and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.

View 7 Replies View Related

Copy Cells In Loop Based On Loop Increment Being Multiplied

Feb 7, 2008

I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,

View 3 Replies View Related

What Command Is Executing Or What Was Last Command Used

Feb 17, 2010

Is there any way to tell (from VBA) what GUI command is currently in progress or what the last command used was?

On a wish list perhaps: Application.LastCommand and/or Application.CurrentCommand

Let's say I'm in the "Sheet_Change" event and want to know what caused the change. Was the event triggered by "Delete", "Paste", "Keystrokes" ...

AutoCAD VBA has "Begin_Command (CommandName as String)" and "End_Command (CommandName as String)" events which I find very useful. Basically I'd like to emulate that to the greatest extent possible.

Application.Caller does not seem to work for the stated purpose. I've been searching for a workaround by means of reading the "Undo Stack". Numerous articles written by experts state that the undo stack is not accessible from VBA.

I've considered reading the text from the "Undo" button's caption but it just seems like such a hack ... not that this whole idea isn't a hack

View 9 Replies View Related

Loop Through Index Worksheets Using For Loop And Select Clause

Nov 4, 2013

I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.

I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.

Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet

[code]....

View 2 Replies View Related

Avoid Changing A Loop Counter Within A Loop?

Oct 24, 2009

I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.

The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.

Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?

View 3 Replies View Related

Copying From One Loop To Paste Into Another Loop?

Aug 27, 2012

I am working on some code that loops through a column of number values. Whenever it encounters a number value and a blank cell in an offset column, it places that number value into an offset cell (forming a separate column to be compared to another column in a separate sheet). I would like to take all the values in that new column and begin placing them in a new column in a separate sheet adjacent to another table. Most of the time, these values should match the adjacent values in the separate worksheet. However, if they don't match, I would like a new row to be created for that mismatched value.

For example. This is the first worksheet. So far, my macro loops through the column with rows 1-5. It looks in the offset cell(0,2) for Isempty value and then places that value into the offset cell(0,6)

text
1
text
text

[Code]....

View 5 Replies View Related

For Each Loop To Delete Row W/ Value (Loop Backwards)

Aug 2, 2007

For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,

Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
Dim counter As Integer
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("c2:c36")

For Each c In rng
If Left(c.Value, 1) "~~" Then
c.EntireRow.Delete
End If
Next c

View 9 Replies View Related

Nested Loop. Inner Loop Not Running

Nov 30, 2006

i have a problem with a nested loop:

it seems like the first instance of the code is running the way i want it to run, but when it starts with the second instance, it does the first search and copy, but it seems like the nested loop is being ignored.

am i doing something wrong?

dan
==========================================================
Thanks to Aaron Blood for the find_range function. i also poached the lastrow function from somewhere on ozgrid, but I cant remember the name of the poster.
==========================================================

Sub new2()

Dim Org_Area As Variant
Dim Item As Variant
Dim Copy_To1 As Variant
Dim Cell_Ref As Variant

r = 1 ..................

View 9 Replies View Related

Command Button Name / Sort By Command Button

Feb 3, 2010

How can I return name of a command button on click? I want to create some sort buttons on a sheet I will use regularly to speed things up. I thought if I could call each sort button by the column letter the button sits in I can use this in a single sub for all buttons

e.g the button sitting in column A is called "A". when i click the button, it returns it's name to a variable which I can then use to sort column A. I know I could create a seperate routine for each button but I was just trying to think of something neater.

View 2 Replies View Related

Run Command Button Click From Another Command Button

Aug 31, 2007

I have a userform with a button on it named But1. Is it possible to store But1 into a variable then activate the But1_Click action using a variable?

Dim strButname As String
strButname ="But1"

Now, how would you activate the But1_Click method using strButname variable. With listboxes you can use the Control(strListboxName) methodology?

This doesn't work, but gives you an idea of what I am trying to do.

Controls(strButname)_click

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved