Solved

Excel Macro User Form to imput parameters

Posted on 2013-06-13
5
405 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
  • 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

758 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

12 Experts available now in Live!

Get 1:1 Help Now