VBA field update

Question

I have a sheet where I have 5 columns

Row  OM    MA    HP      D 
----------------------------------    
1    212   5454  4787    OM
----------------------------------
2    212   5454  4787    MA
----------------------------------
3    212   5454  4787    OM
---------------------------------
4    212   5454  4787    HP
--------------------------------

I want to update OM, MA and HP based on the value in D. So if D=OM I want to make the columns other than OM to be equal to zero.

I have already written the script but I want to learn how to change this script so that the range that the script chooses is dynamic and not specified beforehand and can be updated based on the data we have.

Can anyone help me please?

Thanks in advance,

Here's the script:

Public Sub DataClear()

Dim rgdata As Range
Dim i As Integer

Worksheets("Data").Activate
Set rgdata = Range("A1:E10")
For i = 0 To rgdata.Rows.Count
    If rgdata.Cells(i + 1, "E").Value = "MO" Then
        rgdata.Cells(i + 1, "C").Value = "0"
        rgdata.Cells(i + 1, "D").Value = "0"
    ElseIf rgdata.Cells(i + 1, "E").Value = "MA" Then
        rgdata.Cells(i + 1, "B").Value = "0"
        rgdata.Cells(i + 1, "D").Value = "0"
    ElseIf rgdata.Cells(i + 1, "E").Value = "HP" Then
        rgdata.Cells(i + 1, "B").Value = "0"
        rgdata.Cells(i + 1, "C").Value = "0"
    End If
Next i

End Sub

score 2 · Answer 1 · answered May 18 '14 at 21:44

2

You want to look at a using the MATCH function - when you call it with the range that contains OM through HP, and search for the value in column D, it will give you the offset you need.

Other thing you might consider: step through your data in rows.

Example:

For Each rr in rgdata.Rows
  goodCol = Application.Worksheetfunction.Match(rr.cells(1, lookupCol))
  for ii = 1 To lookupCol - 1
    if ii <> goodCol Then rr.cells(1,goodCol)=0
  next ii
next rr

answered May 18 '14 at 21:44

Floris

45,857
6
70
122

Hadn't considered looping through each row as a `Range` in my answer, seems like it could be a great solution here though – Dan Wagner May 18 '14 at 22:06

Chris Harland · Answer 2 · 2014-05-19T07:38:05.563

2

If you use

LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

You will find the final row that has data in. You can then amend you set statement to

Set rgdata = Range("A1:E" & LastRow)

edited May 19 '14 at 07:38

answered May 18 '14 at 21:47

Chris Harland

456
3
16

1

I think maybe you read the question more carefully than I did... we obviously saw something different but this is surely good advice, unless there are >=65536 rows (as is the case with modern versions of Excel). There are more robust ways of finding the last row... this is "almost" it, but not quite. – Floris May 18 '14 at 21:49

score 2 · Accepted Answer · edited May 23 '17 at 11:50

2

Me too me too...!

Option Explicit
Public Sub DataClear()

Dim Counter As Long, LastRow As Long
Dim DataSheet As Worksheet

'set references, loop boundary and range
Set DataSheet = ThisWorkbook.Worksheets("Data")
With DataSheet
    LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With

'use case statement and for loop to evaluate
With DataSheet
    For Counter = 1 To LastRow
        Select Case .Cells(Counter, 5).Value
        Case Is = "OM"
            .Cells(Counter, 3) = 0
            .Cells(Counter, 4) = 0
        Case Is = "MA"
            .Cells(Counter, 2) = 0
            .Cells(Counter, 4) = 0
        Case Is = "HP"
            .Cells(Counter, 2) = 0
            .Cells(Counter, 3) = 0
        Case Else
        End Select
    Next Counter
End With

End Sub

The script above uses a technique similar to the one you used above (though I think your code has a typo where you are looking for "MO" rather than "OM") combined with techniques for:

Assigning a Sheet variable for easy reference
Identifying the last row (shouts: Error in finding last used cell in VBA)
Implementing a Select...Case statement to cut down on some typing

edited May 23 '17 at 11:50

Community

1
1

answered May 18 '14 at 22:01

Dan Wagner

2,693
2
13
18

+1 - This shows a number of good techniques. The combination of this and other answers ought to be helpful to the OP. Particularly like your more robust "last row" trick, and thank you for providing attribution! That @siddharthrout is a gem, and anyone studying VBA would do well to look at his profile and pick away at some of the top answers. Or go to his blog. – Floris May 18 '14 at 22:22
1

Thanks @Floris, bookmarking his stuff along with the other 5-figure VBA dudes has served me really well haha – Dan Wagner May 18 '14 at 22:26
Thank you all for your help I finally chose to use the solution given by @DanWagner which sounds more professional and neat to me. But thanks everyone again for their propositions. – user2493614 May 19 '14 at 20:15
@DanWagner just two last questions. 1-So whenever I wanted to get the last row value can I use the line you have written there? 2- You use .cells in the case and why you use a dot before? – user2493614 May 19 '14 at 20:19
(1) ALMOST always... you need to be concerned with empty sheets, but you can wrap that line with a protective `If` statement that sets `LastRow = 1` if there is nothing to find. (2) `.Cells` is the advantage offered by a `With...End With` statement, a kind of short-hand (so to speak). MSDN breaks it down here: http://msdn.microsoft.com/en-us/library/wc500chb.aspx – Dan Wagner May 20 '14 at 00:38

VBA field update

3 Answers3