Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    insted cells use range column... (2000 sp 1)

    with this macro how to use the column refrence insted cell reference....?
    Sub MATCH_PER_SECONDO_LIVELLO_MERCATO()

    Dim I, II, III As Long
    Dim MATRICOLA As String
    Sheets("TEMPLATE").Columns("A").NumberFormat = "@"
    'Application.ScreenUpdating = False
    Sheets("TEMPLATE").Range("A2:P5000").ClearContents
    For I = 2 To Sheets("CORPORATE").Range("H" & Rows.COUNT).End(xlUp).Row
    II = Sheets("CORPORATE").Cells(I, "H").End(xlToRight).Column
    For III = Sheets("CORPORATE").Cells(I, "H").Column To II
    With Sheets("GAF").Columns("H")
    Set c = .Find(Sheets("CORPORATE").Cells(I, III).Value, , , xlWhole)
    If Not c Is Nothing Then
    f = c.Address

    Do

    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(1).Resize(, 2).Value = c.Offset(, -7).Resize(, 2).Value
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(, 2).Resize(, 8).Value = c.Offset(, -5).Resize(, 8).Value
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(, 11).Resize(, 3).Value = c.Offset(, 6).Resize(, 3).Value

    Set c = .FindNextŠ

    Loop Until f = c.Address
    End If
    End With

    Next

    If Sheets("CORPORATE").Cells(I + 1, "D").Value <> Sheets("CORPORATE").Cells(I, "d").Value Then
    MATRICOLA = Sheets("CORPORATE").Cells(I, "D").Value
    Call E_MAIL_HANS(MATRICOLA)
    MsgBox "E-MAIL PER " & Sheets("CORPORATE").Cells(I, "D").Value & " COPIATI TUTTI I DATI", vbInformation + vbOKOnly, " FINE INVIO."
    End If

    Sheets("TEMPLATE").Range("A2:P5000").ClearContents
    Sheets("TEMPLATE").Range("A2").Select
    Next

    MsgBox "TUTTE LE E-MAIL INVIATE CORRETTAMENTE", vbInformation

    'Application.ScreenUpdating = True
    End Sub

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

    Re: insted cells use range column... (2000 sp 1)

    In which line or lines?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insted cells use range column... (2000 sp 1)

    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(1).Resize(, 2).Value = c.Offset(, -7).Resize(, 2).Value
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(, 2).Resize(, 8).Value = c.Offset(, -5).Resize(, 8).Value
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(, 11).Resize(, 3).Value = c.Offset(, 6).Resize(, 3).Value

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

    Re: insted cells use range column... (2000 sp 1)

    OK, and what exactly do you want to change?

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insted cells use range column... (2000 sp 1)

    transform the offset statement in:
    Similar:

    Worksheet("TEMPLATE").Range("A"& Rows.Count) = Worksheet("variuos value from GAF").Range("A")
    Worksheet("TEMPLATE").Range("B"& Rows.Count) = Worksheet("variuos value from GAF").Range("D")

    ecc...

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

    Re: insted cells use range column... (2000 sp 1)

    You'd have to declare two variables

    Dim R1 As Long
    Dim R2 As Long

    and use something like this:

    R1 = Worksheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Row
    R2 = c.Row
    Workheets("TEMPLATE").Range("A" & R1) = Workheets("GAF").Range("A" & R2)
    ...

    You must work out the exact columns to use yourself.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insted cells use range column... (2000 sp 1)

    Now is very simple, for me!
    Work fine, not have "simpaty", with cell and offset:-)

    haaaaa lost S in Workheets, but i know you write code on fly, and this is only for wizard....

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insted cells use range column... (2000 sp 1)

    Hoe to permit the copy in TEMPLATE only if column S of GAF existis a value "CE"....

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

    Re: insted cells use range column... (2000 sp 1)

    Do you mean in the same row (row R2)? If so, you can use an If ... End If construction:

    If Worksheets("GAF").Range("S" & R2) = "CE" Then
    ...
    End If

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insted cells use range column... (2000 sp 1)

    insert here but the if condition not consuder "CE" in column S


    R1 = Worksheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Row + 1

    R2 = c.Row


    If Worksheets("GAF").Range("S" & R2) = IDENT_1 Then

    Worksheets("TEMPLATE").Range("A" & R1) = Worksheets("GAF").Range("A" & R2)
    Worksheets("TEMPLATE").Range("B" & R1) = Worksheets("GAF").Range("B" & R2)
    Worksheets("TEMPLATE").Range("C" & R1) = Worksheets("GAF").Range("C" & R2)
    Worksheets("TEMPLATE").Range("D" & R1) = Worksheets("GAF").Range("D" & R2)
    Worksheets("TEMPLATE").Range("E" & R1) = Worksheets("GAF").Range("E" & R2)
    Worksheets("TEMPLATE").Range("F" & R1) = Worksheets("GAF").Range("F" & R2)
    Worksheets("TEMPLATE").Range("G" & R1) = Worksheets("GAF").Range("G" & R2)
    Worksheets("TEMPLATE").Range("H" & R1) = Worksheets("GAF").Range("H" & R2)
    Worksheets("TEMPLATE").Range("I" & R1) = Worksheets("GAF").Range("I" & R2)
    Worksheets("TEMPLATE").Range("J" & R1) = Worksheets("GAF").Range("J" & R2)
    Worksheets("TEMPLATE").Range("K" & R1) = Worksheets("GAF").Range("K" & R2)
    Worksheets("TEMPLATE").Range("L" & R1) = Worksheets("GAF").Range("L" & R2)
    Worksheets("TEMPLATE").Range("M" & R1) = Worksheets("GAF").Range("M" & R2)
    Worksheets("TEMPLATE").Range("N" & R1) = Worksheets("GAF").Range("P" & R2)

    Set c = .FindNextŠ

    End If

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

    Re: insted cells use range column... (2000 sp 1)

    The line

    End If

    should come above the line

    Set c = .FindNextŠ

Posting Permissions

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