Solved

Excel VBA Range question

Posted on 2014-09-25
12
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …

707 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