+ Reply to Thread
Results 1 to 6 of 6

Transposing an Array

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-22-2012
    Location
    OR, USA
    MS-Off Ver
    Excel 14/2010
    Posts
    273

    Transposing an Array

        Set inp_form = ActiveWorkbook.Worksheets("Sheet 1")
        For i = 1 To iCountBox
            cntrng = 12 + i
            inp_form.Range("A" & cntrng).Value = i
            inp_form.Range("B" & cntrng).Value = revcode(i)
            inp_form.Range("D" & cntrng).Value = hcpc(i)
            inp_form.Range("E" & cntrng).Value = modifier(i)
            inp_form.Range("F" & cntrng).Value = iDate(i)
            inp_form.Range("G" & cntrng).Value = unit(i)
            inp_form.Range("H" & cntrng).Value = charges(i)
        Next
    Looking to pass an array into the cells using code similar to this:

        With inp_form.Range("K13:L13").Resize(iCountBox)
            .Value = Application.Transpose(cpt)
            .Sort .Resize(, 1).Offset(, 1), xlDescending, , , , , , xlNo
        End With
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Transposing an Array

    I'm not actually sure what you need help with. Also, I am not a fan of all your input boxes that pop up in your macro. What happens if someone makes a mistake and press enter? How do they go back and fix it?

  3. #3
    Forum Contributor
    Join Date
    03-22-2012
    Location
    OR, USA
    MS-Off Ver
    Excel 14/2010
    Posts
    273

    Re: Transposing an Array

    Mally,

    I am trying to streamline the code from:

    BillScreenPrint:
        Set inp_form = ActiveWorkbook.Worksheets("TEST")
        Rows("13:" & (iCountBox + 13)).Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        For i = 1 To iCountBox
            cntrng = 12 + i
            inp_form.Range("A" & cntrng).Value = i
            inp_form.Range("A" & cntrng).NumberFormat = "0"
            inp_form.Range("A" & cntrng).HorizontalAlignment = xlCenter
            inp_form.Range("B" & cntrng).Value = revcode(i)
            inp_form.Range("B" & cntrng).NumberFormat = "000"
            inp_form.Range("B" & cntrng).HorizontalAlignment = xlCenter
            inp_form.Range("D" & cntrng).Value = hcpc(i)
            inp_form.Range("D" & cntrng).NumberFormat = "00000"
            inp_form.Range("D" & cntrng).HorizontalAlignment = xlCenter
            inp_form.Range("E" & cntrng).Value = modifier(i)
            inp_form.Range("E" & cntrng).HorizontalAlignment = xlCenter
            inp_form.Range("F" & cntrng).Value = iDate(i)
            inp_form.Range("F" & cntrng).NumberFormat = "mm/dd/yy;@"
            inp_form.Range("F" & cntrng).HorizontalAlignment = xlCenter
            inp_form.Range("G" & cntrng).Value = unit(i)
            inp_form.Range("G" & cntrng).NumberFormat = "0"
            inp_form.Range("G" & cntrng).HorizontalAlignment = xlCenter
            inp_form.Range("H" & cntrng).Value = charges(i)
            inp_form.Range("H" & cntrng).NumberFormat = "$#,##0.00_);($#,##0.00)"
            inp_form.Range("H" & cntrng).HorizontalAlignment = xlRight
        Next
        GoTo ErrorCheck
    to something within a line or four. I know it can be done, but I don't understand the code. I am too new to this.

    As far as error correction goes, the bill is printed onto the screen (using the above code) and then:

    ErrorCheck:
        Response = MsgBox("Is the bill correct?", vbYesNo)
        Select Case Response
            Case vbYes
                MsgBox "Congrats"
                GoTo Calculations
            Case vbNo
                GoTo CorrectErrors
        End Select
    
    CorrectErrors:
        iLineItem = InputBox("Enter Line Item You Need to Correct?")
        x = iLineItem
        
        valid = False
            Do Until valid = True
                revcode(x) = Application.InputBox(prompt:="Enter Rev Code for Line Item #" & x, Title:="Rev Code for Line Item #" & x, Left:=300, Top:=250, Type:=1)
                    If revcode(x) <> Empty And revcode(x) <> "False" Then
                        valid = True
                    End If
            Loop
            
            valid = False
            Do Until valid = True
                hcpc(x) = Application.InputBox(prompt:="Enter HCPC/CPT Code for Line Item #" & x, Title:="HCPC/CPT Code for Line Item #" & x, Left:=300, Top:=250, Type:=1)
                    If hcpc(x) <> Empty And hcpc(x) <> "False" Then
                        valid = True
                    End If
            Loop
            
            modifier(x) = Application.InputBox(prompt:="Enter Modifier for Line Item #" & x, Title:="Modifier for Line Item #" & x, Left:=300, Top:=250, Type:=2)
            
            valid = False
            Do Until valid = True
                iDate(x) = Application.InputBox(prompt:="Enter Date of Service for Line Item #" & x, Title:="Date for Line Item #" & x, Left:=300, Top:=250, Type:=2)
                    If iDate(x) <> Empty And iDate(x) <> "False" Then
                        valid = True
                    End If
            Loop
            
            valid = False
            Do Until valid = True
                unit(x) = Application.InputBox(prompt:="Enter Number of Service Units for Line Item #" & x, Title:="Units for Line Item #" & x, Left:=300, Top:=250, Type:=1)
                    If unit(x) <> Empty And unit(x) <> "False" Then
                        valid = True
                    End If
            Loop
            
           valid = False
            Do Until valid = True
                charges(x) = Application.InputBox(prompt:="Enter Charges for Line Item #" & x, Title:="Charges for Line Item #" & x, Left:=300, Top:=250, Type:=1)
                    If charges(x) <> Empty And charges(x) <> "False" Then
                        valid = True
                    End If
            Loop
    It allows them to identify where the error is and correct the whole line.

  4. #4
    Forum Contributor
    Join Date
    03-22-2012
    Location
    OR, USA
    MS-Off Ver
    Excel 14/2010
    Posts
    273

    Re: Transposing an Array

    Unsolved thread. Bumping

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Transposing an Array

    lloydgodin,

    Working off of your original post, it won't be quite that simple. You can avoid the loop, but it will be several range().value = transpose(array). This is because you are using many 1-dimensional arrays instead of a single 2-dimensional array. Here's the code that will avoid the loop though:
        Set inp_form = ActiveWorkbook.Worksheets("Sheet 1")
        
        Range("A13:A" & 12 + iCountBox).Value = Evaluate("Index(Row(1:" & iCountBox & "),)")
        Range("B13:B" & 12 + iCountBox).Value = Application.Transpose(revcode)
        Range("D13:D" & 12 + iCountBox).Value = Application.Transpose(hcpc)
        Range("E13:E" & 12 + iCountBox).Value = Application.Transpose(modifier)
        Range("F13:F" & 12 + iCountBox).Value = Application.Transpose(iDate)
        Range("G13:G" & 12 + iCountBox).Value = Application.Transpose(unit)
        Range("H13:H" & 12 + iCountBox).Value = Application.Transpose(charges)
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Forum Contributor
    Join Date
    03-22-2012
    Location
    OR, USA
    MS-Off Ver
    Excel 14/2010
    Posts
    273

    Re: Transposing an Array

    works great. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1