PDA

View Full Version : [SOLVED] Where To Put Code



doctortt
12-27-2013, 12:46 PM
I'm having trouble with the codes below. When I execute below, nothing happens.

iFindCol = 0iFindCol = Application.WorksheetFunction.Match("Milestone", Sheets("Report Data").Rows(1), 0)
For z = 2 To Sheets("Report Data").Cells(Rows.Count, "A").End(xlUp).Row
If Sheets("Report Data").Cells(z, iFindCol).Value = "Withdrawal" Then
Rows(z).EntireRow.Delete
End If
Next z

doctortt
12-27-2013, 12:51 PM
How come I can't post the codes properly?

Paul_Hossler
12-27-2013, 02:53 PM
1. I don't know why you have problems with the codes :think:

2. Might be the space between the "[" and the "C" in the [ CODE]




iFindCol = 0
iFindCol = Application.WorksheetFunction.Match("Milestone", Sheets("Report Data").Rows(1), 0)
For z = 2 To Sheets("Report Data").Cells(Rows.Count, "A").End(xlUp).Row
If Sheets("Report Data").Cells(z, iFindCol).Value = "Withdrawal" Then
Rows(z).EntireRow.Delete
End If
Next z


To delete rows, you need to start from the bottom and delete your way up



For iRow = Activesheet.UsedRange.Rows.Count To 2 Step -1
If .... Then ActiveSheet.Rows (iRow).Delete
Next iRow



Paul

Trebor76
12-27-2013, 08:30 PM
How come I can't post the codes properly?

You have spaces in the tags - remove the spaces from within the square brackets and all should be fine.

Robert

doctortt
12-30-2013, 06:04 AM
I'm still having trouble with the codes. Can someone please help? I don't want to use the activesheet code because I'd like to have all codes for this Excel workbook on one single worksheet

iFindCol = 0
iFindCol = Application.WorksheetFunction.Match("Milestone", Sheets("Report Data").Rows(1), 0)
For z = Sheets("Report Data").UsedRange.Rows.Count To 2 Step -1
If Sheets("Report Data").Cells(z, iFindCol).Value = "Withdrawal" Then
Sheets("Report Data").Rows(z).Delete
End If
Next z

Paul_Hossler
12-30-2013, 09:29 AM
I don't want to use the activesheet code because I'd like to have all codes for this Excel workbook on one single worksheet.


I don't like to use 'ActviveSheet' either, but what do you mean by the second part?

Paul

doctortt
01-02-2014, 06:19 AM
For example, I have a worksheet called "VBA Control" and I paste all my codes in this worksheet under visual basic. Going back to the spreadsheet, I create buttons and link each sub to the corresponding button.

doctortt
01-02-2014, 06:26 AM
Wops, I pasted my codes to "ThisWorkBook" and it looks like the codes are working now. I'll provide feedbacks again later. Thanks Paul and everyone.

Paul_Hossler
01-02-2014, 06:32 AM
... worksheet called "VBA Control and I paste all my codes in this worksheet under visual basic. Going back to the spreadsheet, I create buttons and link each sub to the corresponding button


A worksheet or a module? There is a difference. While you can have code in a worksheet code module, but I usually only ever put code specific to that worksheet there, like button handlers, etc.

The processing code is in a standard module that is called by the button handler, usually passing parameters

Guess I'm being a little dense today

Paul

doctortt
01-02-2014, 06:40 AM
Right now, my codes are in "ThisWorkbook" under "Microsoft Excel Objects." Should I put the codes under Modules?

SamT
01-02-2014, 10:46 AM
Generally, the Rule of thumb, is:



That Event code specific to one worksheet goes in that sheets Code.
( Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) )
That Event code that applies to the specific Workbook and all it's sheets goes in the ThisWorkbook code.
( Private Sub object_WorkbookOpen(ByVal Wb As Workbook) )
Absolute Rule: Event Code that applies across all open workbooks goes in a Class Module.
( Dim App As Application
Private Sub App_WorkbookActivate(ByVal Wb As Workbook))
That all other code goes in a Module. If more than one Module is used, that they be given descriptive names.

Paul_Hossler
01-02-2014, 11:50 AM
SamT's thumb is right on

If you want, this is a very simple example of where I like to put different pieces of code.

WB code in ThisWorkbook, Worksheet code in the specific WS it applies to, and the 'general' purpose code in a Standard module

Paul

doctortt
01-03-2014, 01:19 PM
Thank you very much, gentlemen.