IF statement to add data when blank
 

In each row i have 7 parts fields - labled "part1" - "part7". If they are
all blank, I want to insert "NPF" into "Part1 (no part fitted)

I cant use find and replace as that only searches individual text boxes.
And my parts could be in any of the 7 parts columns.

Can I do something like this:

IF part1 ="" AND part 2 ="" AND ... part 7 =""
THEN insert "NPF" in part1
Else move to next row

So the code searches row by row

Any help is much appretiated, thank you in advance.

Ernest

(ps. hope it makes sense lol)

IF statement to add data when blank
 

Hi Ernest,

Try something like:
'==============
Sub nTest()
Dim rng As Range
Dim rCell As Range

Set rng = Range("A2:A100")

For Each rCell In rng.Cells
With rCell
If Application.CountA(.Offset(0, 1).Resize(1, 6)) = 0 Then
.Value = "NPF"
End If
End With
Next rCell
End Sub

'<<==============


---
Regards,
Norman



"Ernest Lai" wrote in message
...
In each row i have 7 parts fields - labled "part1" - "part7". If they are
all blank, I want to insert "NPF" into "Part1 (no part fitted)

I cant use find and replace as that only searches individual text boxes.
And my parts could be in any of the 7 parts columns.

Can I do something like this:

IF part1 ="" AND part 2 ="" AND ... part 7 =""
THEN insert "NPF" in part1
Else move to next row

So the code searches row by row

Any help is much appretiated, thank you in advance.

Ernest

(ps. hope it makes sense lol)

IF statement to add data when blank
 

Hi Ernest,

Re-reading your question. change:

For Each rCell In rng.Cells
With rCell
If Application.CountA(.Offset(0, 1).Resize(1, 6)) = 0 Then
.Value = "NPF"
End If
End With

to

For Each rCell In rng.Cells
If Application.CountA(rCell.Resize(1, 7)) = 0 Then
rCell.Value = "NPF"
End If
Next rCell

In this example, the Part1 values are in column A. Adapt to suit your
specific requirements.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Ernest,

Try something like:
'==============
Sub nTest()
Dim rng As Range
Dim rCell As Range

Set rng = Range("A2:A100")

For Each rCell In rng.Cells
With rCell
If Application.CountA(.Offset(0, 1).Resize(1, 6)) = 0 Then
.Value = "NPF"
End If
End With
Next rCell
End Sub

'<<==============


---
Regards,
Norman



"Ernest Lai" wrote in message
...
In each row i have 7 parts fields - labled "part1" - "part7". If they
are
all blank, I want to insert "NPF" into "Part1 (no part fitted)

I cant use find and replace as that only searches individual text boxes.
And my parts could be in any of the 7 parts columns.

Can I do something like this:

IF part1 ="" AND part 2 ="" AND ... part 7 =""
THEN insert "NPF" in part1
Else move to next row

So the code searches row by row

Any help is much appretiated, thank you in advance.

Ernest

(ps. hope it makes sense lol)

IF statement to add data when blank
 

Thanks for the quick replay Norman! I think this would work for 1 column but
i need to search 7 colums say M N O R S T U. All of these columns have to be
blank in say row 1 before i can put "NPF" into M1's cell.

I have finally put my programming hat on and thought of an idea like:

variable X denoting the row (as i need to search 7 columns in each row to be
blank)
Select Range "M1:U1000"

If MX = "" and NX="" .... UX=""
Then MX.Value = "NPF"
Increment X by 1 and have it search the next row


Would something like that work?


Ernest



"Norman Jones" wrote:

Hi Ernest,

Try something like:
'==============
Sub nTest()
Dim rng As Range
Dim rCell As Range

Set rng = Range("A2:A100")

For Each rCell In rng.Cells
With rCell
If Application.CountA(.Offset(0, 1).Resize(1, 6)) = 0 Then
.Value = "NPF"
End If
End With
Next rCell
End Sub

'<<==============


---
Regards,
Norman



"Ernest Lai" wrote in message
...
In each row i have 7 parts fields - labled "part1" - "part7". If they are
all blank, I want to insert "NPF" into "Part1 (no part fitted)

I cant use find and replace as that only searches individual text boxes.
And my parts could be in any of the 7 parts columns.

Can I do something like this:

IF part1 ="" AND part 2 ="" AND ... part 7 =""
THEN insert "NPF" in part1
Else move to next row

So the code searches row by row

Any help is much appretiated, thank you in advance.

Ernest

(ps. hope it makes sense lol)

IF statement to add data when blank
 

Hi Ernest,

Thanks for the quick replay Norman! I think this would work for 1 column
but
i need to search 7 colums say M N O R S T U. All of these columns have to
be blank in say row 1 before i can put "NPF" into M1's cell.

The expression:

If Application.CountA(.Offset(0, 1).Resize(1, 6)) = 0 Then

tests all the relevant columns and returns True only if the are *all* blank

See, however, my last post for a suggested revision.

---
Regards,
Norman



"Ernest Lai" wrote in message
...
Thanks for the quick replay Norman! I think this would work for 1 column
but
i need to search 7 colums say M N O R S T U. All of these columns have to
be
blank in say row 1 before i can put "NPF" into M1's cell.

I have finally put my programming hat on and thought of an idea like:

variable X denoting the row (as i need to search 7 columns in each row to
be
blank)
Select Range "M1:U1000"

If MX = "" and NX="" .... UX=""
Then MX.Value = "NPF"
Increment X by 1 and have it search the next row


Would something like that work?


Ernest



"Norman Jones" wrote:

Hi Ernest,

Try something like:
'==============
Sub nTest()
Dim rng As Range
Dim rCell As Range

Set rng = Range("A2:A100")

For Each rCell In rng.Cells
With rCell
If Application.CountA(.Offset(0, 1).Resize(1, 6)) = 0 Then
.Value = "NPF"
End If
End With
Next rCell
End Sub

'<<==============


---
Regards,
Norman



"Ernest Lai" wrote in message
...
In each row i have 7 parts fields - labled "part1" - "part7". If they
are
all blank, I want to insert "NPF" into "Part1 (no part fitted)

I cant use find and replace as that only searches individual text
boxes.
And my parts could be in any of the 7 parts columns.

Can I do something like this:

IF part1 ="" AND part 2 ="" AND ... part 7 =""
THEN insert "NPF" in part1
Else move to next row

So the code searches row by row

Any help is much appretiated, thank you in advance.

Ernest

(ps. hope it makes sense lol)

IF statement to add data when blank
 

For Each rCell In rng.Cells
If Application.CountA(rCell.Resize(1, 7)) = 0 Then
rCell.Value = "NPF"
End If
Next rCell

In this example, the Part1 values are in column A. Adapt to suit your
specific requirements

When you say columnA is in the above example. Do you mean CountA? So If i
wanted comlumn M I would put CountM?

Sorry to be a pain I am really new at macro and very rusty on programming
(havnt touched it in a long long while)

Thanks again

Ernest

IF statement to add data when blank
 

Hi Ernest,

If your Part1 values are stored in column M (say M2:M100, allowing for a
header row), try:

'================
Public Sub nTest2()
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range

Set rng = Range("M2:M100") '<<====== CHANGE to suit

For Each rCell In rng.Cells
If Application.CountA(rCell.Resize(1, 7)) = 0 Then
rCell.Value = "NPF"
End If
Next rCell
End Sub
'<<================


If the last rows is not known but can be determined from (say) column A,
then try:

'================
Sub nTest3()
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim LRow As Long
Const col As String = "A" '<<===== CHANGE

LRow = Cells(Rows.Count, col).End(xlUp).Row

Set rng = Range("M2").Resize(LRow - 1)

For Each rCell In rng.Cells
If Application.CountA(rCell.Resize(1, 7)) = 0 Then
rCell.Value = "NPF"
End If
Next rCell
End Sub
'<<================

Change : Const col As String = "A" to reflect the column used to determine
the last row of data.


---
Regards,
Norman



"Ernest Lai" wrote in message
...
For Each rCell In rng.Cells
If Application.CountA(rCell.Resize(1, 7)) = 0 Then
rCell.Value = "NPF"
End If
Next rCell

In this example, the Part1 values are in column A. Adapt to suit your
specific requirements

When you say columnA is in the above example. Do you mean CountA? So If
i
wanted comlumn M I would put CountM?

Sorry to be a pain I am really new at macro and very rusty on programming
(havnt touched it in a long long while)

Thanks again

Ernest

IF statement to add data when blank
 

Thanks very much Norman! Works a treat.

Ernest

Just going to tinker around with my file to get it to work in the middle.

"Norman Jones" wrote:

Hi Ernest,

If your Part1 values are stored in column M (say M2:M100, allowing for a
header row), try:

'================
Public Sub nTest2()
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range

Set rng = Range("M2:M100") '<<====== CHANGE to suit

For Each rCell In rng.Cells
If Application.CountA(rCell.Resize(1, 7)) = 0 Then
rCell.Value = "NPF"
End If
Next rCell
End Sub
'<<================


If the last rows is not known but can be determined from (say) column A,
then try:

'================
Sub nTest3()
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim LRow As Long
Const col As String = "A" '<<===== CHANGE

LRow = Cells(Rows.Count, col).End(xlUp).Row

Set rng = Range("M2").Resize(LRow - 1)

For Each rCell In rng.Cells
If Application.CountA(rCell.Resize(1, 7)) = 0 Then
rCell.Value = "NPF"
End If
Next rCell
End Sub
'<<================

Change : Const col As String = "A" to reflect the column used to determine
the last row of data.


---
Regards,
Norman



"Ernest Lai" wrote in message
...
For Each rCell In rng.Cells
If Application.CountA(rCell.Resize(1, 7)) = 0 Then
rCell.Value = "NPF"
End If
Next rCell

In this example, the Part1 values are in column A. Adapt to suit your
specific requirements

When you say columnA is in the above example. Do you mean CountA? So If
i
wanted comlumn M I would put CountM?

Sorry to be a pain I am really new at macro and very rusty on programming
(havnt touched it in a long long while)

Thanks again

Ernest