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

    Add value in vertiocal and horizontal .... (2000 s

    ... have this two sheet
    If in all cells column N and S not is correspondence "CE" in N and "CE" in S put the value in column H in sheet SCARTI in the last free cell in orizontal range and last free cell in vertical range....

    Example attached with final result

    pheraps solved with the code above, but difficult, to intrcept if in Vertical and Horizonta in SCARTI already existis the value finded...
    Example in vertical and orizontal in SCARTI existis 7 i cannot insert the value.... have this dificult...:-(

    My code (tell me if is correct, signature:Your student):
    Sub SCARTI()
    Dim ULTIMA_CELLA As String
    Dim ULTIMA_COLONNA As Long

    Dim RIGA As String
    RIGA = 2
    ULTIMA_CELLA = Worksheets("SCARTI").Cells(Rows.COUNT, "G").End(xlUp).Row + 1
    ULTIMA_COLONNA = Sheets("SCARTI").Cells(2, "IV").End(xlToLeft).Column
    While Not Sheets("GAF").Range("A" + RIGA) = ""
    If Not Sheets("GAF").Range("N" + RIGA) = "" Then
    If Sheets("GAF").Range("N" + RIGA) = "CE" And Sheets("GAF").Range("S" + RIGA) = "" Then
    Sheets("SCARTI").Range("G" + ULTIMA_CELLA) = Sheets("GAF").Range("H" + RIGA)
    ULTIMA_CELLA = ULTIMA_CELLA + 1
    ULTIMA_COLONNA = ULTIMA_COLONNA + 1
    Sheets("SCARTI").Cells(2, ULTIMA_COLONNA) = Sheets("GAF").Range("H" + RIGA)
    End If
    End If

    RIGA = RIGA + 1
    Wend

    End Sub

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

    Re: Add value in vertiocal and horizontal .... (2000 s

    You can use the Find method to test if the value has already been added. You have MANY examples of using Find.

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

    Re: Add value in vertiocal and horizontal .... (20

    OK for find method, but you not tell me about my "big code", like it?

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

    Re: Add value in vertiocal and horizontal .... (20

    As far as I can tell, it should work correctly.

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

    Re: Add value in vertiocal and horizontal .... (20

    As usual head fired...
    See my code not controll in horiz and vertical if already existis a value ...

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

    Re: Add value in vertiocal and horizontal .... (20

    Try this:

    Sub Scarti()
    Dim Ultima_Riga As Long
    Dim Ultima_Colonna As Long
    Dim Riga As String
    Dim Settore As String
    Dim wshG As Worksheet
    Dim wshS As Worksheet

    Set wshG = Worksheets("Gaf")
    Set wshS = Worksheets("Scarti")

    Riga = 2
    Ultima_Riga = wshS.Cells(Rows.Count, "G").End(xlUp).Row
    Ultima_Colonna = wshS.Cells(2, "IV").End(xlToLeft).Column

    Do While Not wshG.Range("A" & Riga) = ""
    If Not wshG.Range("H" & Riga) = "" Then
    If wshG.Range("N" & Riga) = "CE" And wshG.Range("S" & Riga) = "" Then
    Settore = wshG.Range("H" & Riga)
    If wshS.Range("G2:G" & Ultima_Colonna).Find( _
    What:=Settore, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
    Ultima_Riga = Ultima_Riga + 1
    wshS.Range("G" & Ultima_Riga) = wshG.Range("H" & Riga)
    Ultima_Colonna = Ultima_Colonna + 1
    wshS.Cells(2, Ultima_Colonna) = wshG.Range("H" & Riga)
    End If
    End If
    End If
    Riga = Riga + 1
    Loop
    End Sub

Posting Permissions

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