Solved

Excel Macro User Form to imput parameters

Posted on 2013-06-13
5
443 Views
Last Modified: 2013-06-25
I have a macro that works and is working fine.  This is the code.

Sub DeleteZeros()
Dim ws As Worksheet
Dim I As Long, FinalRow As Long

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
    

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For I = FinalRow To 12 Step -1
    If (Cells(I, 19).Value = "") Then GoTo lastline
        If (Cells(I, 19).Value = 0#) And (Cells(I, 23).Value = 0#) And (Cells(I, 27).Value = 0#) Then

'Rows(I).Interior.ColorIndex = 2
Rows(I).Delete Shift:=xlUp
        End If
lastline:
    Next I
    
    Next ws

End Sub

Open in new window


What it does, is searches a worksheet for zeros, and if all 3 have zeros it deletes the line, and skips null lines to preserve formatting.  And runs the process through all of the worksheets in a workbook.   this works great however the end user has a number of report to generate with this code with varying column numbers and counts.

User Form
I created this user form to handle the input.  Here is what I need to do.

work this out so the form launches, and the user can choose how many columns to search (1,2 or3), what those columns are (Ideally by column letter, and I am not sure how to convert that yet) and what and what to search for.  

I would like to set default values to the original macro.  3 columns selected, those values and to search for 0#.

And I have no idea how to make this work.  :(

I may even be doing this the wrong way, I think I need to pass the parameters from the form to the macro using variables.  I am not sure how to make it understand the column count, or how to add additional AND statements with each check box to add or remove how many columns.  

And I am not sure how to convert the column number to the column letter.

I have done things like this in Access before and it does not seem to work quite the same way and I am a little lost.

Thanks for your help.
0
Comment
Question by:larspanky
[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
  • 4
5 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39246211
Hi,

And I am not sure how to convert the column number to the column letter.

You will probably find every "Expert" can propose a different method.

Here is just one...

To display the alphanumeric column reference for column number 12:

MsgBox Split(Columns(12).Address(ColumnAbsolute:=False), ":")(0)

BFN,

fp.
0
 

Author Comment

by:larspanky
ID: 39246427
I will give that a try.  Thank you.

Any thoughts on the column selection, or how I can call only parts of the macro with the form?

Or would it be better to write the macro into the form?
0
 

Author Comment

by:larspanky
ID: 39258150
The customer changed the scope slightly so I now only need to include one column.  

The user would input the one column they want to search, by column letter from A-ZZ and I would prefer to allow a variable for what they are searching for but the requirement is only zeros.  

I am open to any method, I can scrap the user form if its easier to use a MSGbox.  If you can help me get that done, I can close this and award points.

How would you do it?
0
 

Accepted Solution

by:
larspanky earned 0 total points
ID: 39264750
This is the code I ended up delivering.  They were happy with only selecting one column.

The change produces an input box and processes the macro based on whatever column you select.  Works Great.

Sub DeleteZeros_SingleColumn()
Dim ws As Worksheet
Dim I As Long, FinalRow As Long
Dim x As Range
'Converts selected column to integer
Dim y As Integer
On Error Resume Next
'Select Cells in Column to process
Set x = ActiveCell
'of selection select whole column
y = x.Column
'input box to select column with mouse
Set x = Application.InputBox("Select the column to Search using the mouse", Type:=8)
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
    
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For I = FinalRow To 12 Step -1
'Skip null value rows
    If (Cells(I, y).Value = "") Then GoTo lastline
        If (Cells(I, y).Value = 0#) Then
Rows(I).Delete Shift:=xlUp
        End If
lastline:
    Next I
'Scroll to next worksheet
    Next ws

End Sub

Open in new window

0
 

Author Closing Comment

by:larspanky
ID: 39274211
I worked it out on my own.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 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