Excel VBA Range question

Hi,

How do I select Column B as the range for an action in column A?

Example: I want to insert a value in any blank cell in column A but only within the range of populated cells in column B.

Thanks in Advance,

swjtx99
swjtx99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Why is this a VBA question? Why not just Excel's own functionality instead?

It sounds like you should use Data - Data Validation, and select the cells in column B as the range that the cells in column A can use.
0
swjtx99Author Commented:
Hi,

Thanks for your suggestion. I am forced to work in VBA for this task.

Regards,

swjtx99
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then use Data Validation in VBA - something like this:
    With Columns(1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$B$1:$B$9999"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

swjtx99Author Commented:
Hi,

I've tried to apply your suggestion with no l luck. Perhaps if I  provided an example worksheet?

Regards,

swjtx99
Book1.xlsx
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Just insert it into a subroutine, like the attached.
Book1.xlsb
0
swjtx99Author Commented:
Hi Phillip,

I downloaded your attachment and ran it and it didn't work. I found/figured out a way to do it and have attached an example file.

    On Error Resume Next
    ' Look in column A
    With Columns(1)
        ' For blank cells, fill with !ERROR
        .SpecialCells(xlCellTypeBlanks).Value = "!ERROR"
    End With
    Err.Clear
Book1.xlsm
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Apparently your question was different to the one I thought you were asking.

Unfortunately your solution is not 100% effective - try inserting a value into B30, leaving B29 blank, and run your macro.
0
swjtx99Author Commented:
Hi Phillip,

You are correct, however on  my sheet, column B will never be blank therefore the solution works, even if the blanks in column A are not contiguous. Still, there may be a pitfall of which I am not aware so if anyone has that insight and/or a better solution, the points are still available.

Thanks in advance,

swjtx99
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
for introw =1 to 9999
    if cells(introw,2)="" then
        cells(introw,1)="!ERROR"
    end if
next

Open in new window


Alternatively, do you really need a value? What about a color (use Conditional Formatting)?
0
swjtx99Author Commented:
Hi Philip,

Your code filled every blank in column A with "!ERROR" after the last row in column B. It should have filled only blanks in column A where Column B was not blank down to the last row in column B.

This seems to work:

Sub Macro1()
    Columns("A:B").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Value = "!ERROR"
End Sub

swjtx99
0
swjtx99Author Commented:
Hi,

Just discovered a problem. Runtime error if there were no blanks. This seems to have solved that problem:

    Columns("A:B").Select
    On Error GoTo 500
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Value = "!ERROR"
500
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
swjtx99Author Commented:
Need to close out this question and it seems as though I solved it myself. I don't know if this is allowed but I'll try it and see!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.