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

Hello everybody.

I have a macro that checks for column A2 to A12 and if data is found (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 and 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("enter a quantity for " & cel)
next

there is one issue.

The macro is returning the error when finding a row with text and no value on same line for column B but when I click OK it sends a 1 to the 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 msgBox asking for the quantity and having it filling it up as going through all the lines. Thank you.

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

Thank you.
Albert

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus.

Many thanks for your input.

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

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.

If possible a input box, with the possibility of Cancel, asking for 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,
Albert

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2