Check for values in column A and if found then column B must have value.

[Alberto Viveiros]

Hello everybody.

I have a macro that checks for column A2 to A12 and if data is foun
(text) the column B2 to B12 must have a numeric value.

In other words, if A2 has got text then B2 must have a numeric value an
so on, if not stop macro.

There is the following code;

for each cel in range("a:a")
if isempty(cel) then exit for ' finish on empty cell
if isempty(cel.offset(,1)) then cel.offset(,1).value = msgbox("ente
a quantity for " & cel)
next

there is one issue.

The macro is returning the error when finding a row with text and n
value on same line for column B but when I click OK it sends a 1 to th
empty field.

Can I have the focus set to the next empty cell please?

If possible when error found either focus on the empty cell or a msgBo
asking for the quantity and having it filling it up as going through al
the lines. Thank you.

I must say I thought this to be the most complicated code of all I hav
but is quite simple or I mean a few lines and its doing waht I though t
be many lines of code.

Thank you.
Alber

 

[Claus Busch]

Hi Alberto,

Am Fri, 8 Mar 2013 11:17:34 +0000 schrieb Alberto Viveiros:

for each cel in range("a:a")
if isempty(cel) then exit for ' finish on empty cell
if isempty(cel.offset(,1)) then cel.offset(,1).value = msgbox("enter
a quantity for " & cel)
next

try:
Dim rngC As Range

For Each rngC In Range("A1:A12")
If IsEmpty(rngC.Offset(0, 1)) Then
MsgBox "enter a quantity for " & _
rngC.Offset(0, 1).Address(0, 0)
End If
Next


Regards
Claus Busch

 

[Alberto Viveiros]

Hi Alberto,

Am Fri, 8 Mar 2013 11:17:34 +0000 schrieb Alberto Viveiros:
-

try:
Dim rngC As Range

For Each rngC In Range("A1:A12")
If IsEmpty(rngC.Offset(0, 1)) Then
MsgBox "enter a quantity for " & _
rngC.Offset(0, 1).Address(0, 0)
End If
Next


Regards
Claus Busch

Hi Claus.

Many thanks for your input.

However, this is returning a question for every line even though the
are either full or empty.

The macro must look at all full cells on A1:A10 and then if A1 has tex
or data then B1 must have numeric value, if A2 has text or data then B
must have numeric value and so on until last empty line found.

If possible a input box, with the possibility of Cancel, asking fo
value and then macro would fill in the empty cell.

Hope anybody out there has got the answer for this one.

Many thanks for your time and a very nice weekend to everybody.

Cheers,
Alber

 

[Claus Busch]

hi Alberto,

Am Fri, 8 Mar 2013 20:10:35 +0000 schrieb Alberto Viveiros:

The macro must look at all full cells on A1:A10 and then if A1 has text
or data then B1 must have numeric value, if A2 has text or data then B2
must have numeric value and so on until last empty line found.

try:
For Each rngC In Range("A1:A12")
If Len(rngC) = 0 Then
ElseIf IsEmpty(rngC.Offset(0, 1)) Then
MsgBox "enter a quantity for " & _
rngC.Offset(0, 1).Address(0, 0)
End If
Next


Regards
Claus Busch