Posts by Shenlung

  • Loop Column A nested Loop column B, copy same values, skipping some matching values

    Re: Loop Column A nested Loop column B, copy same values, skipping some matching valu


    Updated with workbook and any further info I could think of.


    Added two lines inside the If statement. First line of code is to show where I put it. It has not helped.


    Code
    rd.Cells(j, 1).Delete Shift:=xlUp ' delete number from sheet1 col A
            i = i + 1 ' force the loop to run the previous cell in column B again
            j = j + 1 ' force the loop to run the previous cell in column A again


    Thought maybe it was skipping cells due to the delete, so I forced it to re check the "new" cell that had moved up instead of moving up a cell immediately... obviously didn't help, but I'm getting a little desperate.

  • Loop Column A nested Loop column B, copy same values, skipping some matching values

    Hello again, Ozgridians,


    I am trying to run a simple search and move function, looping through column B; on every cell in Column B, it loops through Column A, if it finds a matching value, it copies the values from Sheet1(RawData) to column A and B on Sheet2(Clean), then deletes the cells with a shift up. It is looping from the bottom to the top, but it keeps skipping certain values, no matter how many times the script is run.


    VBA code:


    And a copy of the workbook as it stands now.


    I just need to see why it is skipping values... they are not obviously different(text in one column, number in the other), and they are not being copied but not deleted, so I'm at a loss. Sheet 3 contains a copy of the original number list so I can just copy it in and re-test my code.


    For some odd reason, I cannot upload my workbook. I'll keep trying to update this post with the book.


    Edit: Had to upload it as a zip file, the main file is 26 KB too large. Virus scan and all that stuff before you play with it, never know what kind of nasties are flying around.

  • Syntax error in DoCmd.RunSQL Access command

    Re: Syntax error in DoCmd.RunSQL Access command


    Thanks Craig, that seems to have fixed the syntax error, now I'm pulling a 3709 error in the section of code above this one and I just had 3 other projects dumped in my lap, so this thing is going on the backburner. I'll update this thread when I can verify that the code is 100% :D

  • Syntax error in DoCmd.RunSQL Access command

    Can I possibly get someone to look this snippet of code over and tell me where the syntax error is? I'm kludging a database together in Access and I just know I have a comma in the wrong place or something stupid like that, but I can't locate it.


    Also, I'm trying to get the userform to read a 10 digit barcode that will be a 9 digit social security number followed by an "M" or "D"(medical or dental); So that scanning the barcode will fill the ssn line, then put the 10th digit in the txtRecordType field and then the macro will cycle through the If statements...anyone have some solutions?



    Thanks in advance!

  • Insert new row in table when one empty row remains via vba

    Re: Insert new row in table when one empty row remains via vba


    Still looking for an answer to the question:


    Is there a way to insert the row IN the table, but BELOW the selected cell; especially when the selected cell is the last one in the table?


    Interesting thought that I am not sure how to implement:


    instead of inserting a row below the last row on the table, could the code insert a row below the table itself, then expand the table down one row? That would increase the variable "mp" by 1, effectively breaking the loop, but allow the user to manipulate the last cell without having blank rows inserted above the final row in the table... Someone have a bit of code in their head to do that?


    I'll just keep editing this post to make things easy...


    Code thus far:

    Code
    'Set rd = Sheets("ROLL UP")
        mp = ListObjects("Table14").ListRows.Count + 1
        While Cells(mp, "B") <> ""
        Sheet1.Cells(mp + 1, "B").EntireRow.Insert
        rd.ListObjects("Table14").Resize Range("$A$1:$AD$" & mp + 1)
        mp = ListObjects("Table14").ListRows.Count
        Wend


    Now, this code runs to the EntireRow.Insert, inserts the row below the table, then resets to the "Set rd = " line. I am guessing that it has something to do with the mp range variable changing, but I had to force the code to reset mp at the end because it was initially holding the mp variable even after inserting a row in the table. Anyone want to guess why this thing goes into an infinite loop and refuses to go past inserting a row without resetting the entire macro? The best part of this thing is that it's in a Worksheet_Change(ByVal Target As Range) sub, so I cannot F8 into it, I have to put breaks and F5 through the thing on every iteration. If anyone wants to check this thing on a test workbook, the attachment is on the first post, replace the code on sheet1 with


    best of luck.

  • Insert new row in table when one empty row remains via vba

    Re: Insert new row in table when one empty row remains via vba


    Nevermind, I knew it was simple... I don't suppose there is a way to make it insert the row IN the table, but BELOW the selected cell? I have it inserting from the last cell, but it inserts above the cell, so deleting the rows and then inputting data on the last row will force the macro to create the blank row in between the last row and the one prior.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
   
   Set rd = Sheets("ROLL UP")
        mp = ListObjects("Table14").ListRows.Count
        While Cells(mp, "B") <> ""
        Sheet1.Cells(mp + 1, "B").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
        mp = ListObjects("Table14").ListRows.Count
        Wend
end sub

    Still open to suggestions though.

  • Insert new row in table when one empty row remains via vba

    Hello again,


    This code is vexing me, and I'm not sure why. I know it's something simple, but I'm lost... I am trying to make a code that will "watch" a column in a table and insert a new row when there is only one blank row left in the table.

    Code
    Set rd = Sheets("ROLL UP")
    rup = rd.Range("E250").End(xlUp).Row
    mp = ListObjects("Table14").ListRows.Count + 1
        While mp = rup
        Sheet1.Cells(mp, "B").Select
        Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
        Wend


    I was thinking that maybe a "For" loop might work better, but I know a while will work. I'm teaching myself this stuff, so the code is not the most elegant, suggestions and improvements are assuredly welcome; explanations are being sought as well.forum.ozgrid.com/index.php?attachment/37893/

  • Print a number of sheets using a User Form

    Re: Print a number of sheets using a User Form


    It's going to be something along these lines, but I am not too good at code without a workbook to test it on.

    Code
    Sub PrintList()
r = listbox1
For i = 1 To r
    Sheets("r").Select
    ExecuteExcel4Macro _
        "PRINT(1,,,1,,,,,,,,2,""Printer1"",,TRUE,,FALSE)"
Next i
End Sub



    If possible, a sample workbook would make it easier to modify the code to work with your situation.

  • Error Referencing Next Sheet

    Re: Error Referencing Next Sheet


    Ok, tested this and it works, assuming that you don't add ANYTHING between the creation of the new sheet and it's renaming.



    This renames whatever sheet is active to Series 3. It only works because it happens immediately following the creation of the new sheet, so application focus is on the new blank sheet. I can check no further into the code bacause I do not have the rest of the sheets the code references. The variable idea didn't work because the new sheet created is the next in a series based on what was created before. it works on the first iteration, (it creates Sheet 3), but the next itereation, it creates "Sheet 4" regardless of how many sheets there actually are. Probably has something to do with the way the sheet codenames are hard coded, so even deleting "Sheet 2" will not make the next sheet "Sheet 2", it will be whatever the next number in the sequence is.

    ...re-reading that, it made little sense, but the code works.

  • Error Referencing Next Sheet

    Re: Error Referencing Next Sheet


    I haven't actually worked with a macro like this before, but...

    Have you tried making "Sheet 3" into a variable instead of a constant?

    Add something to the effect of

    Code
    NextSh = "Sheet" & Sheets(Sheets.Count)



    Then change your "Sheet 3" reference immediately following the "Sheets.Add After:=Sheets(Sheets.Count)" to "NextSh" prior to the rename. After it renames it, you can hard reference it.

    Just an idea, of course.

  • Increment cell value after printing(code question)

    Hello all,

    I had a question regarding a piece of code that was being worked on in these forums, but didn't want to hijack the thread.

    The thread is here, the code in question is as follows:



    The question is, after changing the range value to B16, I tested the code and it incremented cell c31! I changed the code to be

    Code
    Range("B16").Value = Range("B16") + 1
                
                Case Else ' cell is empty
                    Range("B16").Value = 1


    (notice the removed {.} before the Range ojects)and it incremented the proper cell. Any ideas as to why the first code didn't work? I ask so I can learn, thanks to these forums, I'm getting better at vba every day, but I have so much more to learn! The spreadsheet being used is http://www.ozgrid.com/forum/at…mentid=37342&d=1301653644

    Thanks in advance,
    Shen

  • Increment numeric value in cell when printing worksheet

    Re: Increment numeric value in cell when printing worksheet


    Ah, I misunderstood your initial request. I took AAE's code and modified it, it now prints the page, then increments the warrant number and exits sub. This Page details how to make a command button and assign a macro to it. here's the code:


    Virtually all credit for the code goes to AAE, the rest goes to "Record Macro" :P

  • Can conditional formatting return text

    Re: Can conditional formatting return text


    Rory, How would one go about doing that? I think it pertains to the op's quandry, and it may be an easier solution that would allow the record to revert should cell B get cleared(If I'm understanding it correctly).

  • Can conditional formatting return text

    Re: Can conditional formatting return text


    You can copy and paste it in right after one of the "End Sub"s and before the next (In the worksheet you want the macro to run in) and it will create the breaks for you. When an "End Sub" occurs, the macro clears it's variables, assuming it is a private sub(If I remember correctly, I'm kind of new to this). Best solution is to copy the workbook and put the script into the copy to test it out, so you do not run the risk of killing something vital. I do not think it will hurt anything, but you never know.

  • Can conditional formatting return text

    Re: Can conditional formatting return text


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
LRange = Cells.Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To LRange
        If Sheet1.Cells(i, "B") > "0" Then
        Sheet1.Cells(i, "A").Clear
        Sheet1.Cells(i, "A") = "Done"
        End If
    Next i
End Sub



    Change it into a worksheetchange function like this and put it in the worksheet object. I also changed it to start at row 2, so the headers will be left alone. Change the "For i = 2 To LRange" Number part to whatever number row you want it to start looking at.

    Also, this will not put the validation list back on the sheet after it changes it to "done".

  • Can conditional formatting return text

    Re: Can conditional formatting return text


    I made a slight miscalculation. LRange should be "B" not "A". Here's the fixed code:

    Code
    Sub ClearFormatting()
On Error Resume Next
LRange = Cells.Range("B" & Rows.Count).End(xlUp).Row
    For i = 1 To LRange
        If Sheet1.Cells(i, "B") > "0" Then
        Sheet1.Cells(i, "A").Clear
        Sheet1.Cells(i, "A") = "Done"
        End If
    Next i
End Sub


    And a source workbook:forum.ozgrid.com/index.php?attachment/37325/