Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If else... (2002)

    I'm confused with setting this up.

    MsgBox Msg1, vbYesNo
    If yes: run code, then show MsgBox Msg2, vbYesNo, If yes, run same code again and keep showing Msg2 until No, at which point show Msg3.
    If No: show Msg3.

    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If else... (2002)

    If MsgBox("Do you want to continue?", vbQuestion + vbYesNo) = vbYes Then
    Do
    ' some code here
    ...
    Loop Until MsgBox("Do you want to run the code again?", vbQuestion + vbYesNo) = vbNo
    End If
    MsgBox "Thank you and goodbye!", vbInformation

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If else... (2002)

    Thanks Hans, but I am still wrong somewhere:

    Dim Msg1 As String
    Dim Msg2 As String
    Dim Msg3 As String

    Msg1 = vbCrLf & xyz & vbCrLf & xyz 'etc etc
    Msg2 = vbCrLf & xyz & vbCrLf & xyz 'etc etc
    Msg3 = vbCrLf & xyz & vbCrLf & xyz 'etc etc


    MsgBox Msg1, vbYesNo
    If MsgBox(Msg1 = vbYes) Then
    Do
    Application.ScreenUpdating = False
    Sheets("Rejections 1").Select
    Dim q As Long
    Dim w As Long
    w = Cells(Rows.Count, 9).End(xlUp).Row
    For q = 2 To w
    If Cells(q, 1) = "BC069-02" Then
    Cells(q, 9) = Cells(q, 9) / 10
    End If
    Next q
    Sheets("Summary").Select
    Range("A1").Select
    Application.ScreenUpdating = True
    MsgBox Msg2, vbYesNo

    Loop Until MsgBox(Msg2 = vbYesNo) = vbNo
    End If
    MsgBox Msg3, vbInformation


    Added: Hold up, I think I've got it....

    Added: No still wrong.......

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If else... (2002)

    You've got the syntax of MsgBox wrong. Please read my previous reply.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If else... (2002)

    Oops, got there eventually. Thanks.
    I have a code module (attached), much of which you have assisted me with previously. It has been added to and modified somewhat over the last few months (including today) and has slowed down significantly. Would you mind taking a quick glance to see if there is anything that you can see that would cause it to run slower than it "could". <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>sorry, it's a little long, I'm just curious if anything (bad structure....) 'jumps out' at you!
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If else... (2002)

    You're opening and closing lots of files (it's weird to have text files with an .xls extension by the way), copying and pasting entire sheets, and looping through the rows of some of them. If the amount of data in the sheets is large, it's bound to be slow.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If else... (2002)

    We use batch commands to copy several text files and merge them into one as an xls. When opening the xls. files, you are faced with the text import wizard.

    There is a lot of date, probably 1,200 rows at max, and I expect it to be slow for the reasons you state. But it's slower than it originally was and I can't put my finger on why. Not to worry, it's way faster than human fingers!!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If else... (2002)

    The .xls extension is normally used for Excel workbooks. If the text import wizard appears, the file is not an Excel workbook but a text file, so the .xls extension is inappropriate; it would have been better to use .txt.

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If else... (2002)

    I'm looking for the easiest way to:
    Find last data row on 4 different sheets, copy from there upto row 2 (entire rows) and paste into 5th sheet. The first lot of data will go in row 2 on sheet 5, but the other 3 sets of data will go below. One of the 4 sheet's may not always contain data. How best to structure?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If else... (2002)

    If a sheet has no data, would it be completely empty or would there still be headers in row 1?
    And is there a column that will always be filled in every row with data, for example column A?

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If else... (2002)

    1. There will still be a header.
    2. Column I will always contain a value.
    Thanks
    (whilst you are there) <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> >> If Not Left(Range("J" & r, 2)) = "11" Then << I need to do OR "80" if poss?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If else... (2002)

    Does this do it?

    If Not (Left(Range("J" & r, 2)) = "11" Or Left(Range("J" & r, 2)) = "80") Then

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If else... (2002)

    Try this:
    <code>
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim m As Long
    Dim n As Long
    Set wshTarget = Worksheets("...")

    ' ***** Repeat this block for each of the source sheets *****
    ' First available row on target sheet
    m = wshTarget.Range("I" & wshTarget.Rows.Count).End(xlUp).Row + 1
    Set wshSource = Worksheets("...")
    ' Last used row
    n = wshSource.Range("I" & wshTarget.Rows.Count).End(xlUp).Row
    ' Only if there are data
    If n > 1 Then
    wshSource.Range("A2:A" & n).EntireRow.Copy _
    Destination:=wshTarget.Range("A" & m)
    End If
    ' ***** End of block to repeat *****
    </code>
    You must substitute names for ... of course.

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If else... (2002)

    I get "arguement not optional"

    Dim n As Long
    Dim r As Long
    Dim z As Long
    Dim rng1 As Range
    Dim rng2 As Range

    Sheets("0808 Update").Select

    n = Range("I5000").End(xlUp).Row 'Last data row number

    Set rng1 = Range("J2:J" & n)
    For r = n To 2 Step -1
    If Not (Left(Range("J" & r, 2)) = "11" Or Left(Range("J" & r, 2)) = "80") Then
    Range("J" & r).EntireRow.Delete
    End If
    Next r

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If else... (2002)

    The error was already present in the code that you posted, I merely copied it.

    Here is the correct line:

    If Not (Left(Range("J" & r), 2) = "11" Or Left(Range("J" & r), 2) = "80") Then

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •