Solved

Excel VBA Range question

Posted on 2014-09-25
12
68 Views
Last Modified: 2014-10-10
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
0
Comment
Question by:swjtx99
  • 7
  • 5
12 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40344976
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
 

Author Comment

by:swjtx99
ID: 40345030
Hi,

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

Regards,

swjtx99
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40345550
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
 

Author Comment

by:swjtx99
ID: 40347179
Hi,

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

Regards,

swjtx99
Book1.xlsx
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40349901
Just insert it into a subroutine, like the attached.
Book1.xlsb
0
 

Author Comment

by:swjtx99
ID: 40351211
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40351683
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
 

Author Comment

by:swjtx99
ID: 40356858
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40356865
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
 

Author Comment

by:swjtx99
ID: 40361338
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
 

Accepted Solution

by:
swjtx99 earned 0 total points
ID: 40361395
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
 

Author Closing Comment

by:swjtx99
ID: 40372550
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Search field that goes to row 7 42
Win7/Excel 2016 crashing 1 183
kendo spreadsheet data return 15 72
Excel IF/OR formula not working. 8 55
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now