Help with range

**jmcconville18** · 06-19-2015, 10:01 AM

I have a workbook that I want to take all the information in specific cells and put them into a database. I have created a string called myCopy, but when i set it as a range it's giving me an error.

HTML Code:

here is the attached workbook
Chemical Field Ticket.xlsm

thank you

**Arkadi** · 06-19-2015, 10:18 AM

You can't set a range like that... but try this one:

Sub UpdateLogWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As Variant
    Dim myCell As Range
    
    'cells to copy from Input sheet - custName, wellName,
    'contractorName , rigNum, delivTicket, Date,
    'prodName1, qty1, unitSize1, unitPrice1, Total
    
    
    
 myCopy = Array("F35", "C6", "C7", "C8", "C9", "C10", "C11", "B15", "C15", "D15", "E15", "F15", "B16", "C16", "D16", "E16", "F16", "B17", "C17", "D17", "E17", "F17", "B18", "C18", "D18", "E18", "F18", "B19", "C19", "D19", "E19", "F19", "B20", "C20", "D20", "E20", "F20", "B21", "C21", "D21", "E21", "F21", "B22", "C22", "D22", "E22", "F22", "B23", "C23", "D23", "E23", "F23", "B26", "C26", "F26", "B27", "C27", "F27", "B28", "C28", "F28", "B29", "C29", "F29", "B30", "C30", "F30", "F32", "F33", "F34", "B33", "B35")

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Database")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy(0))
        For i = 1 To UBound(myCopy)
            Set myRng = Union(myRng, .Range(myCopy(i)))
        Next i

    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

Changes in red.

**Arkadi** · 06-19-2015, 10:31 AM

I don't follow... specific questions are hard to answer with no context....

I can tell you what you are trying to do exactly, but I'll try to figure out what the code does

**Arkadi** · 06-19-2015, 10:36 AM

The range is probably in order, the first cell would be C6, not F35 regardless of how the range is set up (which was F35 first)

**jmcconville18** · 06-19-2015, 10:38 AM

I can see that it did in fact add the total to the end of the database. so it must just be the order it puts it in

**jmcconville18** · 06-19-2015, 10:28 AM

thank you very much. This fixes the error but I want the total "F35" to be in column C in the database. It won't record the f35 value and is showing up blank. is that because the array needs to be in order from cells left to right?

**Arkadi** · 06-19-2015, 10:28 AM

ty for the rep jmcconvile

Does that mean it worked for you? If so, please mark the thread as solved?
Oh I see you posted...

**jmcconville18** · 06-19-2015, 10:34 AM

no it shouldn't. It's a formula based on the subtotal and sales tax on the excel sheet. I'm just trying to reorder this in the database and have it put F35 into column C

**jmcconville18** · 06-19-2015, 10:37 AM

is there anyway to change that?

**Arkadi** · 06-19-2015, 10:44 AM

Bit hard to do this without the data... but I went back to using the array instead of the range for the part that copies values... see if it does what you want?

Sub UpdateLogWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As Variant
    Dim myCell As Range
    
    'cells to copy from Input sheet - custName, wellName,
    'contractorName , rigNum, delivTicket, Date,
    'prodName1, qty1, unitSize1, unitPrice1, Total
    
    
    
 myCopy = Array("F35", "C6", "C7", "C8", "C9", "C10", "C11", "B15", "C15", "D15", "E15", "F15", "B16", "C16", "D16", "E16", "F16", "B17", "C17", "D17", "E17", "F17", "B18", "C18", "D18", "E18", "F18", "B19", "C19", "D19", "E19", "F19", "B20", "C20", "D20", "E20", "F20", "B21", "C21", "D21", "E21", "F21", "B22", "C22", "D22", "E22", "F22", "B23", "C23", "D23", "E23", "F23", "B26", "C26", "F26", "B27", "C27", "F27", "B28", "C28", "F28", "B29", "C29", "F29", "B30", "C30", "F30", "F32", "F33", "F34", "B33", "B35")

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Database")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy(0))
        For i = 1 To UBound(myCopy)
            Set myRng = Union(myRng, .Range(myCopy(i)))
        Next i
myRng.Cells(1, 1).Select
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For i = 0 To UBound(myCopy)
            historyWks.Cells(nextRow, oCol).Value = inputWks.Range(myCopy(i))
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

**sktneer** · 06-19-2015, 11:08 AM

You can of course set a range like that but in your case the myCopy string is exceeding the 255 characters limit.
To handle this situation, you may declare two strings like myCopy1 and myCopy2 and then use Union to set a range.

Try this.....

Sub UpdateLogWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy1 As String, myCopy2 As String
    Dim myCell As Range
    
    'cells to copy from Input sheet - custName, wellName,
    'contractorName , rigNum, delivTicket, Date,
    'prodName1, qty1, unitSize1, unitPrice1, Total
    
    myCopy1 = "F35,C6,C7,C8,C9,C10,C11,B15,C15,D15,E15,F15,B16,C16,D16,E16,F16,B17,C17,D17,E17,F17,B18,C18,D18,E18,F18,B19,C19,D19,E19,F19,B20,C20,D20,E20,F20,B21,C21,D21,E21,F21,B22,C22,D22,E22,F22,B23,C23,D23,E23,F23,B26,C26,F26,B27,C27,F27,B28,C28,F28,B29,C29,F29"
    myCopy2 = "B30,C30,F30,F32,F33,F34,B33,B35"
    


    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Database")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = Union(.Range(myCopy1), .Range(myCopy2))

    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

**jmcconville18** · 06-19-2015, 11:37 AM

thanks for your help. With this code i have the same problem. It seems to be assigning each cell to the database columns by numeric order. The F35 cell is still being put in the last column of the database even though it's shown 1st on the list

**jmcconville18** · 06-19-2015, 11:10 AM

with that last code i get "Invalid next control variable reference". the cell on the input sheet displays $477

Chemical Field Ticket.xlsm

**Arkadi** · 06-19-2015, 11:39 AM

oops needs fiixng.

**jmcconville18** · 06-19-2015, 01:37 PM

using your last response I still get an error eluding to Next myCell. it says invalid next control variable reference

**Arkadi** · 06-19-2015, 01:56 PM

OOOOh... doh thought i'd fixed before pasting... change "next myCell" to "next i"

**jmcconville18** · 06-19-2015, 02:37 PM

Here is what I have. The red is where the error comes up saying Variable not defined

HTML Code:

**jmcconville18** · 06-19-2015, 02:44 PM

the next error I get, after defining i as long, is "variable not defined" for the myCopy in

HTML Code:

**jmcconville18** · 06-19-2015, 02:36 PM

what variable should I define "i" as? I defined it as a long after receiving the "variable not defined" error

**Arkadi** · 06-19-2015, 02:39 PM

Well that's not with my posted code, but yeah in my version it would need a dim i as long

**Arkadi** · 06-19-2015, 02:53 PM

That's because it is a combination of 2 versions... mine being part of it. With this:

Sub UpdateLogWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As Variant
    Dim myCell As Range
    Dim i as Long
    
    'cells to copy from Input sheet - custName, wellName,
    'contractorName , rigNum, delivTicket, Date,
    'prodName1, qty1, unitSize1, unitPrice1, Total
    
    
    
 myCopy = Array("F35", "C6", "C7", "C8", "C9", "C10", "C11", "B15", "C15", "D15", "E15", "F15", "B16", "C16", "D16", "E16", "F16", "B17", "C17", "D17", "E17", "F17", "B18", "C18", "D18", "E18", "F18", "B19", "C19", "D19", "E19", "F19", "B20", "C20", "D20", "E20", "F20", "B21", "C21", "D21", "E21", "F21", "B22", "C22", "D22", "E22", "F22", "B23", "C23", "D23", "E23", "F23", "B26", "C26", "F26", "B27", "C27", "F27", "B28", "C28", "F28", "B29", "C29", "F29", "B30", "C30", "F30", "F32", "F33", "F34", "B33", "B35")

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Database")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy(0))
        For i = 1 To UBound(myCopy)
            Set myRng = Union(myRng, .Range(myCopy(i)))
        Next i
myRng.Cells(1, 1).Select
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For i = 0 To UBound(myCopy)
            historyWks.Cells(nextRow, oCol).Value = inputWks.Range(myCopy(i))
            oCol = oCol + 1
        Next i
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

it is probably closer, but I think you said something about $477 ending up somewhere, not sure what that was about, but I had forgotten to change "next myCell" to next i.. which I fixed here.

**jmcconville18** · 06-19-2015, 02:56 PM

that works! thanks

**Arkadi** · 06-19-2015, 02:59 PM

finally! Sorry that took so long. Glad we got it sorted out

Hybrid View

Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Re: Help with range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count number of occurances below a range, within a range, and below a range

[SOLVED] Copy data from a range of cells into a blank range based on common cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions