Newbie - Simple code needed

[topstar74]

Hello folks,

This is probably very easy for all you VBA experts here. I am primarily an Oracle Developer, and at this point of time have to suddenly for whatever reason code a bit of VBA in an Excel spreadsheet. I would like a simple task done. Could someone please help me achieve this basic code.

I have a spreadsheet with nearly 20,000 rows of data. It has 5 columns
SlNo
PDate
PDesc
PAmount
PCategory

The code should loop through each of the rows where PCategory is empty and do the following -

if PDesc = "Gauges" then the value of Pcategory should be "Quality Control"

Basically this is it!

Thanks in advance

topstar74

[PAB]

Hi topstar74,

Give this a go!

Sub Quality_Control()
Dim row As Long
For row = 1 To Cells(Rows.Count, 3).End(xlUp).row
If Cells(row, 3).Value = "Gauges" Then
Cells(row, 5).Value = "Quality Control"
End If
Next row
End Sub
Regards,
PAB

[Paul_Hossler]

With 20,000 rows, you might want some status while it running



'recommended to force explicit variable declarions
Option Explicit
Sub Quality_Control_1()

'Dim row As Long -- row is key word
'using the Excel object model
Dim rRow As Range, rData As Range

'if by some change there is missing data in col 3, this will only use the row of the last filled cell
' Cells(Rows.Count, 3).End(xlUp).row

Set rData = ActiveSheet.UsedRange
If rData.Cells.Count = 1 Then Exit Sub ' empty sheet

'turn off screen updating for performance
Application.ScreenUpdating = False
For Each rRow In rData.Rows
With rRow
Application.StatusBar = "Now processing row " & Format(.row, "#,##0") & " out of " & Format(rData.Rows.Count, "#,##0")
If .Cells(3).Value = "Gauges" Then .Cells(5).Value = "Quality Control"
End With
Next
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub



Paul

[topstar74]

Thank you PAB!

It works. However, from reading it, I guess in the If statement below....


If Cells(row, 3).Value = "Gauges" Then
Cells(row, 5).Value = "Quality Control"
End If



the code is not checking to make sure the Pcategory column is empty first?

I would want the value "Quality Control" applied, only if the value in that cell is empty. If it already has someother value, it should not overwrite that.

This will do the trick I suppose?

If Cells(row, 3).Value = "Gauges" and Cells(row,5) = "" Then
Cells(row, 5).Value = "Quality Control"
End If


Thanks!

[topstar74]

With 20,000 rows, you might want some status while it running

Nice! Thanks Paul. I like the status update!

There is a little more to this please....! What code do I need to change for the following line....

.Cells(3).Value = "Gauges"


If Pdesc contains something like "Contour Gauges" or "Gauges Contour" or basically beginning, ending or containing the word "Guages" anywhere in it...

what should I change the = sign to?

[Bob Phillips]

.Cells(3).Value Like "*Gauges*"

[Paul_Hossler]

I missed that 'Only if Empty' requirement


If .Cells(3).Value Like "*Gauges*" And Len (Trim(.Cells(5).Value)) = 0 Then .Cells(5).Value = "Quality Control"


Paul