Macro Loops through 10 rows, find's next cell value > 0 places X in adjacent cell & Stops.

**MIACG** · 05-10-2013, 02:49 PM

Hi All,

I am looking to create a macro that will loop through a column of at most 10 rows. The macro needs to identify the next number > 0 in the column, set the cell value of the adjacent cell to "X" then stop. When the macro is executed again, it needs to delete the "X" it just created prior to identifying the next number > 0 and placing an "X" in it's corresponding adjacent cell. If the macro has reached the bottom of the range it needs to go back to the top of the range and begin again.

I'm feeling pretty stuck here and could use some guidance. Attached please find the template for reference.

Thanks in advance,

MIACG

X_Loop.xlsm

**tehneXus** · 05-10-2013, 03:05 PM

like this ?
X_Loop.xlsm

contains:

Option Explicit

Private Sub cmdStart_Click()
    Dim xlRng As Range
    Dim i As Integer
    Dim lngStart As Integer, lngEnd As Integer, lngCol As Integer
    lngStart = 7
    lngEnd = 16
    lngCol = 4
    Set xlRng = Range(Cells(lngStart, lngCol + 1), Cells(lngEnd, lngCol + 1)).Find(What:="x", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not xlRng Is Nothing Then
        xlRng.ClearContents
        If xlRng.Row <> lngEnd Then
            lngStart = xlRng.Row + 1
        End If
    End If
    
    For i = lngStart To lngEnd
        If Val(Cells(i, lngCol).Value) > 0 Then
            Cells(i, lngCol + 1).Value2 = "X"
            Exit Sub
        End If
    Next i
End Sub

**MIACG** · 05-10-2013, 03:25 PM

A million thanks to you man. That works brilliantly! Really appreciate your help here.

I'm pretty new to VBA but I'm trying to learn while I can. So I understand, this macro sets the range based on the start and ending position. Then it identifies an X. If it finds the X, it clears the contents from the cell. If the "X"s position is not at the end of the range then it begins its next position one row below where it cleared the contents of the x. Then the variable "i" is determined in conjunction with the column to determine if the value is > 0, whereby it will execute to the next column (lngCol +1 ) a value of "X". Just curious why do you denote Value2 as such instead of Value?

**tehneXus** · 05-10-2013, 03:30 PM

Hi,

it works exactly like you described, for 'value2' see this description from MS: http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

In this macro it makes no difference but since I know value2 I don't use value any more while working with date/time values, and as I am doing this a lot I am using value2 for everything

Regards

Macro Loops through 10 rows, find's next cell value > 0 places X in adjacent cell & Stops.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro Loops through 10 rows, find's next cell value > 0 places X in adjacent cell & Stops.

Re: Macro Loops through 10 rows, find's next cell value > 0 places X in adjacent cell & St

Re: Macro Loops through 10 rows, find's next cell value > 0 places X in adjacent cell & St

Re: Macro Loops through 10 rows, find's next cell value > 0 places X in adjacent cell & St

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions