Posts by 5nizza

Re: Excel VBA: GoTo statements- is it possible to use variable in the line's name

Thank you. The post is very helpful...I got plenty of ideas to move on:)

Is it possible to include a variable in the line's name for GoTO statement?? Example:

Sub test()
Dim a As String
Dim year As String
year = "2010"
if a= 0 then GoTo Lable & year
End If


Lable2010:
'test


End Sub

this is just simplified code of what I'm looking for. Year is consntatly changing, and there're lables for each year in the code.... I didn't want to write a long code like

if year="2011" then GoTo Lable2011
ElseIf year="2010" then GoTo Lable2010
ElseIf year="2009" then GoTo Lable2009
End If


Lable2011:
Lable2010:
Lable2009:

Hello everyone,

Below is an example of the worksheet I'm currently working on. Currently, there're about 3000 records in the table. Here's what I would like to calculate, and having a hard time figuring out how:

• For example, for August,2011 (column A) count how many items in column B is from January 2008. So,I'm ignoring dates comletely, only month and year are relevant. The answer should return number 2. Or if we counting it for for Feb 2011 it should return 2...
• Same should work for all other month(and other dates) in column A. So for July,2011 the answer should be 0,and for June,2011 it should be 1

[TABLE="class: grid"]

A

B

Disposition Date

Commenced Date

08/01/2011

01/01/2008

08/01/2011

01/20/2008

08/02/2011

02/03/2009

08/03/2011

02/07/2010

08/03/2011

02/03/2010

07/03/2011

03/25/2011

07/01/2011

05/04/2008

06/01/2011

01/15/2008

06/03/2011

05/01/2008

[/TABLE]

Any idea how to calculate it? I tried everything from COUNTIF to different SUMs and other basic formulas....

Re: assigning an address to a variable

i think following should be added

Set rpart = Range(issuescreen.ComboBox2.List(i, 5)).[COLOR=red]address[/COLOR]

Re: Macro to delete rows containing multiple criteria

I modified my code a bit:

Sub delete_row()
Dim ndialed As Variant
Dim time As Variant
Dim day As Variant
Dim rng As Range
Dim rng2 As Range
Dim i As Long
Dim x As Long
'change your criteria below
ndialed = Array("10", "40", "192", "244") 'you can add your criteria
time = Array("9:00:00", "10:00:00 ", "16:00:00") 'you can add your criteria
day = "saturday" 'change you day here
 For x = LBound(time) To UBound(time)
        'Sheet with the data, you can also use Sheets("MySheet")
        With ActiveSheet
            'Firstly, remove the AutoFilter
            .AutoFilterMode = False
            Rows("1:1").Select
            Selection.AutoFilter
            'Apply the filter
            ActiveSheet.Range("B1:B" & .Rows.Count).AutoFilter Field:=1, Criteria1:=time(x)
            ActiveSheet.Range("B1:B" & .Rows.Count).AutoFilter Field:=2, Criteria1:=day
            Set rng = Nothing
            With .AutoFilter.Range
                On Error Resume Next
                Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                          .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rng Is Nothing Then
                    'second filder
                    For i = LBound(ndialed) To UBound(ndialed)
                    ActiveSheet.Range("B1:B" & .Rows.Count).AutoFilter Field:=3, Criteria1:=ndialed(i)
                     Set rng2 = Nothing
                     With ActiveSheet.AutoFilter.Range
                     On Error Resume Next
                     Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                          .SpecialCells(xlCellTypeVisible)
                     On Error GoTo 0
                        If Not rng2 Is Nothing Then
                        rng2.EntireRow.Delete
                        End If
                    End With
                    ActiveSheet.Range("B1:B" & .Rows.Count).AutoFilter Field:=3
                    Next i
End If
End With
End With
Next x
ActiveSheet.AutoFilterMode = False
End Sub

P.S.: You're gonna ask to add 4th item, aren't you? lol

Re: Macro to delete rows containing multiple criteria

here's my version

Sub delete_row()
Dim ndialed As Variant
Dim time As Variant
Dim rng As Range
Dim rng2 As Range
Dim i As Long
Dim x As Long
'change your criteria below
ndialed = Array("10", "40", "192", "244") 'you can add your criteria
time = Array("9:00:00", "10:00:00 ", "16:00:00") 'you can add your criteria
 For x = LBound(time) To UBound(time)
        'Sheet with the data, you can also use Sheets("MySheet")
        With ActiveSheet
            'Firstly, remove the AutoFilter
            .AutoFilterMode = False
            Rows("1:1").Select
            Selection.AutoFilter
            'Apply the filter
            ActiveSheet.Range("B1:B" & .Rows.Count).AutoFilter Field:=1, Criteria1:=time(x)
            Set rng = Nothing
            With .AutoFilter.Range
                On Error Resume Next
                Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                          .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                If Not rng Is Nothing Then
                    'second filder
                    For i = LBound(ndialed) To UBound(ndialed)
                    ActiveSheet.Range("B1:B" & .Rows.Count).AutoFilter Field:=3, Criteria1:=ndialed(i)
                     Set rng2 = Nothing
                     With ActiveSheet.AutoFilter.Range
                     On Error Resume Next
                     Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                          .SpecialCells(xlCellTypeVisible)
                     On Error GoTo 0
                        If Not rng2 Is Nothing Then
                        rng2.EntireRow.Delete
                        End If
                    End With
                    ActiveSheet.Range("B1:B" & .Rows.Count).AutoFilter Field:=3
                    Next i
End If
End With
End With
Next x
ActiveSheet.AutoFilterMode = False
End Sub

Re: Macros stop functioning when workbook is password protected

Well, I had a similar issue, which I wasn't able to research. I think the only way to buypass it, is to tell macro to unlock the workbook in the beginning, and lock it back again after it's done. But again, I'm not an expert. let's see what others think.

Re: How to use =MATCH when spaces are involved

I would advise you to paste a sample of your workbook, because I have no idea what's in B7 cell in your spreadsheet...

Re: Run macro in multiple worksheets and save sheets individually

you can definitely paste any code you want where you have "MY CODE HERE, depending on the action to take." It will work with the curent active sheet, and when it's done it will move on to the next one.

Re: Clear range of cells with blanks but leave cells with data

Quote

What is the variant 23 used for?

I don't know... sorry

Quote

I guess I should have previous said the blank cells are "". Would this make a difference?

well...I'm a bit confused. Would you mind posting a sample of what you're working with?

Quote

As you can tell I am not familiar with SpecialCells.

if you want to know more, you can read here and here

Re: Clear range of cells with blanks but leave cells with data

Quote

I want to clear cells with blanks and leave the cells with formulas.

do you mean you want to clear everything but leave formulas in? if true than you can use

Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Application.CutCopyMode = False
    Selection.ClearContents

Re: Macro to compare columns move to new tab

change it to

ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-10]>=" & rng1 & ",RC[-10]=RC[-9]),""true"",""false"")"

btw, you don't need to enter number with equal(=) sing....just a number will do

Re: Run macro in multiple worksheets and save sheets individually

i changed it a bit..how about

Sub WorksheetLoop()
   Dim sht As Worksheet
   For Each sht In ActiveWorkbook.Worksheets
   sht.Activate
   Range("A:A").Select
Selection.TextToColumns _
      Destination:=Range("A1"), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, _
      Semicolon:=True, _
      Comma:=False, _
      Space:=False, _
      Other:=False, _
      OtherChar:="-"
      
      MsgBox sht.Name
   Next sht
End Sub

Re: Macro to compare columns move to new tab

So, i tried pervious macro at work, and it worked...but i still changed it just in case...

Sub test()
Dim e As String
Dim msg As Integer
Dim cell As Range
Application.DisplayAlerts = False
Sheets(1).Activate
b = "a2"
e = Range(b).SpecialCells(xlCellTypeLastCell).Row
e = Range(Left(b, 1) & e).Address
msg = InputBox("Please neter a number to begin searching for duplicates")
If msg < 0 Then
MsgBox ("Please enter value greater than 0 and  try again")
Else
For Each cell In Range(b, e)
If cell.Value = msg And cell.Value >= 8 Then 'change 8 to any other number to set your minimum
    If cell.Value = cell.Offset(0, 1).Value Then
    Range(cell.Address).EntireRow.Select
    Selection.Cut
    Sheets(2).Activate
    Range(b).End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
    End If
End If
Next cell
End If
Application.DisplayAlerts = True
End Sub

Re: Macro to compare columns move to new tab

ha..you know what, when I did it at work, it gave me that error too. However, when I came home and did it again, it was fine...Give me sometime later today I'll rework it a bit.

Re: Copy data from one workbook to another

Quote

I have two workbooks that I am opening with VB code

I assume you were starting with the blank workbook with no data, where you would paste the code to open two workbooks

Quote

Example of a few of the cells that need to be copied are
Workbook 1 Workbook 2
B1 B3
B2 B4
B3 B5

I understood that you want data from workbook 1(b1,b2,b3) and 2(b3,b4,b5) copied to that new worksbook...

Quote

When I add that to my code I get an error that says "cannot change part of a merged cell". What is this code supposed to do?

and how am I suppose to know that you have some merged cells, when it's a first time you're mentioning it?

Quote

What is this code supposed to do?

if you paste it in blank workbook, it opens workbook, it will open workbook 1 and 2 and copy cells specified above.

Quote

It seems backwards to me because it is opening up the destination file first and then the source file that contains the data second.

Oh, so you're saying workbook QuoteTemplate.xlsm was the one you wanted to paste info from Quote.txt?

Quote

Did I not explain what I am trying to do clearly above?

Unfortunately no.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

Re: sum cells from tables in another worksheet

so, how big is your real sheet? because if it's as small as in the example, than you better of just using SUM function for cellson Summary sheet. Macro will not do any good here...

Re: Calculate age differences when entered as yy.mm

um...formula:

=RA-CA

So, using your sheet if you put in M5: =L5-K5, than the result will be 1.00, for M6 it's 4.19, etc...Is it not what you're looking for?

Re: Counting Out of Stocks

I did a search and found following topic. I tested it on yours and it works

=SUM(IF(FREQUENCY(IF(B2:K2=1,COLUMN(B2:K2)),IF(B2:K2<>1,COLUMN(B2:K2)))>=1,1))

when you paste the code into the cell, don't hit enter, but CONTROL+SHIFT+ENTER.

Re: Copy data from one workbook to another

something like that? Just add path to your files and paste it in the code below instead of red text

Sub test()
Application.ScreenUpdating = False
Workbooks.Open Filename:="[COLOR=red]C:\Users\...\Documents\QuoteTemplate.xlsm[/COLOR]"
Range("B3", "B5").Copy ThisWorkbook.Sheets(1).Range("b1")
Workbooks.Open Filename:="[COLOR=red]C:\Users\...\Documents\Quote.txt[/COLOR]"
Range("B1", "B3").Copy ThisWorkbook.Sheets(1).Range("a1")
Workbooks("Quote.txt").Close
Workbooks("QuoteTemplate.xlsm").Close
Application.ScreenUpdating = True
End Sub