Solved

Excel VBA Range question

Posted on 2014-09-25
12
70 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
calibration tool for multiple spreadsheets 8 70
Help with merging Excel Documents 6 48
Calculate number of weeks in a month in MS Excel 2013 12 81
Excel formula 5 74
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Viewers will learn various types of data validation for different data types in Excel 2013.
Video by: Zack
Viewers will learn the basics of using Excel Tables, the benefits found with them, and some pitfalls.

776 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