CODEWITHSUNDEEP
excelvba
t0rres
t0rres

Reputation: 211

Replace values in rows after certain GAP of row numbers

I need to replace the value in a certain row that is repeated after a certain amount of rows. I have multiple Excels I need to do this.

Current data:

7th row: IN
16th row: IN ( comes after 9 rows) 
25th row: IN ( comes after 9 rows)

I need these values to be replaced by OUT.

I did some research and seems like I could use macros but I am not familiar with macros.

Can anyone please help with macros with a loop or suggest any other ideas?

enter image description here

Upvotes: 0

Views: 300

Answers (4)

Error 1004
Error 1004

Reputation: 8220

You could try:

Option Explicit

Sub test()

    Dim LastRow As Long, i As Long
    Dim arr As Variant

    'Change target worksheet if needed
    With ThisWorkbook.Worksheets("Sheet1")
        'Find the last row of column A
         LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
         'Set in arr the range with the data starting from row 1 to last row
         arr = .Range("A1:A" & LastRow)

         For i = LBound(arr) To UBound(arr) - 1
            'Check if the current and the next Arr(i) are both "IN"
            If arr(i, 1) = "IN" And arr(i + 1, 1) = "IN" Then
                'Set the next arr(i) to OUT
                arr(i + 1, 1) = "OUT"
            End If

         Next i
         'Print the values
         .Range("A1:A" & UBound(arr)).Value = arr

    End With

End Sub

Upvotes: 0

Giovanni Patruno
Giovanni Patruno

Reputation: 763

For Excel Office 365, create a macro going under View -> Macro -> View Macros -> You input a macro name and then press Create button.

A text editor screen should appear, the macro should be the following:

Sub test_macro()
    Dim searching_string As String
    searching_string = "IN"
    replacing_string = "OUT"
    searching_column = "A"
    minimum_distance_to_be_modified = 3
    previous_found_row = -1
    row_number = 10000
    For i = 1 To row_number
        If Range(searching_column + CStr(i)).Value = searching_string Then
            If i - previous_found_row <= minimum_distance_to_be_modified And previous_found_row <> -1 Then
               Range(searching_column + CStr(i)).Value = replacing_string
            End If
            previous_found_row = i
         End If
    Next
End Sub

Set your searching_string, searching_column, minimum_distance_to_be_modified, replacing_string and you should be fine!

I did a test with the settings that you find in the snippet and this was the result:

enter image description here

Hope that this is going to help you.

Upvotes: 1

t0rres
t0rres

Reputation: 211

I solved my problem via macros and VB code.

 Sub Macro1()
'
' Macro1 Macro
'

Dim i As Integer
i = 7

Do While i <= Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 1).Value = "Out"
i = i + 9
Loop
End Sub

Add this code to new macros and run the macros if anyone is having a similar problem to mine.

Upvotes: 0

Guy Louzon
Guy Louzon

Reputation: 1203

A simple excel formula can work per sheet, and of course a macro can work

The excel solution, starting in B2

=IF(AND($A1 = "IN",$A1 = $A2),"Out",$A2)

This formula will replicate your original column, with the fix. then a simple copy & paste as values of column B to A should work

A VBA solution, for which you need to select the relevant column:

Sub fixOut()
 Dim cell As Object
 For Each cell In Selection
    If cell = "IN" AND cell = cell.OffSet(-1, 0) Then cell = "Out"
 Next cell
End Sub

Upvotes: 2

Related Questions