VBA macro to search and edit data in one row

[ansvk1]

Hi there,
Can somone help me in defining the logic for a task i would like to do in excel on a regular basis?

here is how my excel data looks like:[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Task #[/TD]
[TD]Level 1[/TD]
[TD]Level 2[/TD]
[TD]Level 3[/TD]
[TD]Level 4[/TD]
[/TR]
[TR]
[TD]123456789[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]987654321[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Macro should be doing the following steps:

1. go to each task#, look for non empty cell value starting from the farthest end (column header Level 4). only for that row
2. when found a value, it (farthest non empty, Level#only) should be replaced with numberic value 1.
3. now delete all the other values in rest of the columns Level1,2,3 (to the left of non empty cell) if there is any value at all upto column Level 1. But not task # cell value.
4. now steps 1 through 3 has to be looped for multiple rows (as many as task#s present).

Any help is greatly appreciated!
thanks.

 

[richh]

I would first sort on Task #
Search for task number
Col = 2
If worksheet.Cells(row, 1).value = TheTastNumberYoureLookingFor Then
while(Is Not Blank(worksheet.Cells(row, Col).Value) = True) 'Assumes there will be values in each column up to the last one
Col = Col +1
End Loop

Alter the last Cell to = 1
LastCol = Col
Reset Col = 2
For Col to LastCol-1
worksheet.Cells(row, Col).Value = ""
Next Col


Something along those lines

 

[Dim Me as xlNoob]

Hi there,
Macro should be doing the following steps:

1. go to each task#, look for non empty cell value starting from the farthest end (column header Level 4). only for that row
2. when found a value, it (farthest non empty, Level#only) should be replaced with numberic value 1.
3. now delete all the other values in rest of the columns Level1,2,3 (to the left of non empty cell) if there is any value at all upto column Level 1. But not task # cell value.
4. now steps 1 through 3 has to be looped for multiple rows (as many as task#s present).

thanks.

This macro should loop through all task numbers in Column A (assuming a header is in Row 1) and complete steps 2 and 3 for each task:

Sub EditTaskRows()
    Dim cell As Range, rng As Range
    Dim lngCol As Long, sh As Worksheet
    
    Set sh = ActiveSheet
    With sh
        Set rng = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    
    For Each cell In rng
        lngCol = sh.Cells(cell.Row, sh.Columns.Count).End(xlToLeft).Column
        If lngCol <> 1 Then sh.Cells(cell.Row, lngCol).Value = 1
        For i = lngCol - 1 To 2 Step -1
            If i < 2 Then Exit For
            sh.Cells(cell.Row, i).Clear
        Next i
    Next cell
End Sub

 

[ansvk1]

Sub EditTaskRows()
    Dim cell As Range, rng As Range
    Dim lngCol As Long, sh As Worksheet
    
    Set sh = ActiveSheet
    With sh
        Set rng = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    
    For Each cell In rng
        lngCol = sh.Cells(cell.Row, sh.Columns.Count).End(xlToLeft).Column
        If lngCol <> 1 Then sh.Cells(cell.Row, lngCol).Value = 1
        For i = lngCol - 1 To 2 Step -1
            If i < 2 Then Exit For
            sh.Cells(cell.Row, i).Clear
        Next i
    Next cell
End Sub

this code is working exactly to the steps defined, and will be just right for my needs. Amazing logic, simply beautiful!
thanks for the help borther! for some reason, i am unable to wrap my head around logics and syntaxs well yet. Thanks again for the your HELP!

 

[ansvk1]

appreciate your suggestions richh, thanks for trying to get me started atleast. I would have worked on your suggestions, if Dim had not replied .

Regards,
Krishna