Posts by norie

Re: Number DIMs automatically

That code just isn't right, mind you I'm not sure what it's meant to do.

If the first part is meant to be naming ranges it should be like this.

For Q = 1 To 4 
             
   Worksheets("Sheet1").Range("B" & j).Name ="LIMIT " & Q   
   Worksheets("Sheet1").Range("C" & j).Name = "MTOPC" & Q  
   Worksheets("Sheet1").Range("E" & j).Name = "CYCLEC" & Q

Note I've assumed Worksheets(Sheet1) is meant to refer to Sheet1, otherwise the code would fail unless you've declared a variable called Sheet1 somewhere else and given
it a value that is the name of an existing worksheet.

Not sure what's happening here, are ASSIGN1, ADDM1, CLOSING1, ASSIGN2, ADDM2, CLOSING2 etc existing named ranges

Range("ASSIGN" & Q) = Range("CHECKSUM") 
Range("ADDM" & Q) = Range("ADDM" & Q) + Range("ASSIGN" & Q) 
Range("CLOSING" & Q) = Range("CLOSING" & Q) + Range("ASSIGN" & Q)

Also, why are you naming individual cells?

Why not name entire columns or rows of data?

Or even name the entire range.

Re: Number DIMs automatically

You haven't used any variables in that code apart from Q.

"LIMIT" & Q is just a string, and Range("LIMIT" & Q) is referring to a named range called LIMITQ, where Q is 1,2,3,4.

Re: Number DIMs automatically

Why have you declared a whole bunch of variables that you never use, and never need to use?

I can remove all these declarations and the code will still compile.

Dim CHOOSE As Range


Dim CHECKSUM As Range


Dim FRESH As Range


Dim ADDED As Range


Dim ASSIGN1 As Range
Dim ASSIGN2 As Range
Dim ASSIGN3 As Range
Dim ASSIGN4 As Range


Dim CASCADE1 As Range
Dim CASCADE2 As Range
Dim CASCADE3 As Range
Dim CASCADE4 As Range


Dim CLOSING1 As Range
Dim CLOSING2 As Range
Dim CLOSING3 As Range
Dim CLOSING4 As Range


Dim CYCLEP1 As Range
Dim CYCLEP2 As Range
Dim CYCLEP3 As Range
Dim CYCLEP4 As Range


Dim CYCLEC1 As Range
Dim CYCLEC2 As Range
Dim CYCLEC3 As Range
Dim CYCLEC4 As Range


Dim LIMIT1 As Range
Dim LIMIT2 As Range
Dim LIMIT3 As Range
Dim LIMIT4 As Range


Dim MTOPP1 As Range
Dim MTOPP2 As Range
Dim MTOPP3 As Range
Dim MTOPP4 As Range


Dim MTOPC1 As Range
Dim MTOPC2 As Range
Dim MTOPC3 As Range
Dim MTOPC4 As Range


Dim OPEN1 As Range
Dim OPEN2 As Range
Dim OPEN3 As Range
Dim OPEN4 As Range


Dim WITHDRAW1 As Range
Dim WITHDRAW2 As Range
Dim WITHDRAW3 As Range
Dim WITHDRAW4 As Range

Re: Run-time error: 91 - Looping through ListBox to Copy/Paste Selected Items

Change the loop to start at 0 and end at ListCount.

For lItem = 1 To lb.ListCount

You might also want to increment lastRowColD.

Sub enterRecord()
    Dim lastRowColD            As Long
    Dim lItem                  As Long
    Dim lb                     As ListBox
     
     
    Set lb = Sheets("Sheet1").ListBoxes("List Box 1")
     
     
    lastRowColD = Range("D" & Rows.Count).End(xlUp).Row + 1
    For lItem = 1 To lb.ListCount
        If lb.Selected(lItem) = True Then
            Range("C" & lastRowColD).Value = [C2].Value
            Range("D" & lastRowColD).Value = [D2].Value
            Range("E" & lastRowColD).Value = lb.List(lItem)
            lastRowColD = lastRowColD + 1
        End If
    Next lItem
     
End Sub

Re: UDF Gives #value! Error Unless Using Global Variables

When you get !VALUE# where are the variables declared?

Could you attach a workbook with some sample data so we can test the code out?

Re: List tab names of different workbook

All you need is to add a workbook reference to Workheets to refer to the other workbook.

Re: Array of Dates converting to String in Combobox Problem

If you need to convert to a date you use DateValue, or CDate.

You wouldn't need that for the combobox though, only when comparing the dates in the sort sub.

Are you sure you can't attach a sample workbook?

All it would need would be a worksheet with dates and the userform, nothing else.

Re: Array of Dates converting to String in Combobox Problem

Instead of using an array of dates use an array of strings that represent those dates.

Then populate it using the cell's Text property so it's formatted as it's on the worksheet.

You'll need to adjust the sorting sub to use DateValue to convert the items in the array to real dates so you can compare them chronologically.

Re: Newbie Userform questions: Exit event running twice due to SetFocus

I know the tab order goes Textbox1, 2, 3.

I'm suggesting you change it.

Re: Newbie Userform questions: Exit event running twice due to SetFocus

Why are you using SetFocus?

If you want to change the order of the controls then right click the form and select Tab Order.

Re: On Error Resume Next Problem

What you could do is get rid of the On Error ... and then you might find out what the problem is.:)

Then you can decide what to do, perhaps dealing with the problem(s) with some of the methods you mention.

Re: How to skip specific loops in a loop?

William

Are you really looping through the entire range A1:F100?

If you just wanted to check column A for 'yes' and process B:F if it's found you could use something like this.

For Each x In Range("A1:A100")


     If x.Value = "yes" Then
           ' process B:F on same row as x
     End If
Next x

This doesn't skip any iterations of the loop, it just doesn't do anything on every one of them.

Re: Syntax for Range definition...

If wkAudit is a worksheet reference you can set rSearchRange to refer to a range on it like this.

Set rSearchRange = wkAudit.Range("A2:A16137")

Re: Hiding and Showing userforms - 424 Runtime Error

What code, if any, is in the Initialize event of UserForm2?

Re: VBA next object select

You can refer to controls on a userform using it's Controls collection.

Set picture = Me.Controls("image" & k)

Re: VBA Form Textbox format - 2 decimals

You've declared F as Long which is an integer data type, so you lost the decimal part.

Try declaring it as Double.

Re: std. deviation, mean and userform

How can you calculate the standard deviation of 1 number?

Re: std. deviation, mean and userform

What have you tried?

Re: Double click shortcut

What is the format of the times?

Can you attach a small sample workbook?

Re: I don't know what is wrong with this loop!

Where does the code error?

By the way, where are you running this code?