CODEWITHSUNDEEP
vbaexcel
s.milziadi
s.milziadi

Reputation: 705

Fill cell in certain column with data from another sheet

I'm using a VBA script to fill a column with some data.

This script checks for the first free cell in a range and fill it with the data from another Excel worksheet.

The script starts when user double clicks on a data-cell in the other sheet.

The code of the VBA script is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ws2 As Worksheet
Dim cognome As Range
Dim ultB As Long

Set cognome = Me.Range("A:A")
Set ws2 = ThisWorkbook.Sheets("PUBBLICO")
ultB = IIf(ws2.Range("E8").Value = "", 8, ws2.Range("E7").End(xlDown).Row + 1)

If Not Intersect(Target, cognome) Is Nothing And Target.Value <> "" Then
    ws2.Range("E" & ultB).Value = Me.Range("B" & Target.Row).Value 'ANNO
    ws2.Range("F" & ultB).Value = Me.Range("A" & Target.Row).Value 'COGNOME
    'ws2.Range("E4").Value = Me.Range("C" & Target.Row).Value NOME SQUADRA
End If
Set ws2 = Nothing
Cancel = True
End Sub

The problem is that this script should be optimized for another use. I've another Excel sheet that into the range of cell to fill contains a cell that is always pre-filled and it is merged.

This is the example of my Excel file:

enter image description here

As you can see, row 19 is always pre-filled.

So, any suggestions to correct my script to jump row 19?

Upvotes: 0

Views: 2454

Answers (1)

teylyn
teylyn

Reputation: 35905

Consider

ultB = IIf(ws2.Range("E8").Value = "", 8, ws2.Range("E27").End(xlUp).Row + 1)
If ultB = 19 Then ultB = 20
If ultB = 27 Then
    MsgBox "Form is full"
    Exit Sub
End If

If Not Intersect(Target, cognome) Is Nothing And Target.Value <> "" Then

This will find the last populated cell from the bottom up instead of from the top down. It also contains some code to notify when the form is full.

Upvotes: 1

Related Questions