Excel vba to insert into Excel 2007 table

[Biz]

Dear All,

Is there a way to quickly insert 1 job number into Excel 2007 table?

  'Speeding Up VBA Code
    With Application
        .ScreenUpdating = False 'Prevent screen flickering
        .Calculation = xlCalculationManual 'Preventing calculation
        .DisplayStatusBar = False
        .DisplayAlerts = False 'Turn OFF alerts
        .EnableEvents = False 'Prevent All Events
    End With
 
 
some code
 
'Speeding Up VBA Code
With Application
    .ScreenUpdating = True 'Prevent screen flickering
    .Calculation = xlAutomatic 'Preventing calculation
    .DisplayStatusBar = True
    .DisplayAlerts = True 'Turn OFF alerts
    .EnableEvents = True 'Prevent All Events
End With

 

[Andrew Poulsom]

What does the macro recorder give you if you record your actions while doing it manually?

 

[Biz]

Hi Andrew,

I used an array to load numbers in memory then conduct loop and found it took only 1 sec.
Code in Bold Red changes.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

My sample code is below

[COLOR=#1f497d][SIZE=3][FONT=Calibri]Sub cFormatting()<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    Dim rfCells As Range<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    Dim aCell As Variant<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    <o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    Dim myarray As Variant<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    Dim i As Integer<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    myarray = myVRng.Value<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    <o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    'Looping structure to look at array.<o:p></o:p>[/FONT][/SIZE][/COLOR]
[B][COLOR=red][SIZE=3][FONT=Calibri]    For i = 1 To UBound(myarray)<o:p></o:p>[/FONT][/SIZE][/COLOR][/B]
[B][COLOR=red][SIZE=3][FONT=Calibri]        'MsgBox myarray(i, 1)<o:p></o:p>[/FONT][/SIZE][/COLOR][/B]
[B][COLOR=red][SIZE=3][FONT=Calibri]        aCell = Application.VLookup(myarray(i, 1), tRange, 1, 0)<o:p></o:p>[/FONT][/SIZE][/COLOR][/B]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]        If IsError(aCell) Then<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]            If IsEmpty(Range("B6")) Then<o:p></o:p>[/FONT][/SIZE][/COLOR]
[SIZE=3][FONT=Calibri][COLOR=#1f497d]                [/COLOR][B][COLOR=red]Sheets(ws.Name).Cells(65536, Range("B6").Column).End(xlUp).Value = myarray(i, 1) 'if only values are needed: Destination.value=Target.value[/COLOR][COLOR=#1f497d]<o:p></o:p>[/COLOR][/B][/FONT][/SIZE]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]            Else<o:p></o:p>[/FONT][/SIZE][/COLOR]
[SIZE=3][FONT=Calibri][COLOR=#1f497d]                [/COLOR][B][COLOR=red]Sheets(ws.Name).Cells(65536, Range("B5").Column).End(xlUp).Offset(1, 0).Value = myarray(i, 1) 'if only values are needed: [/COLOR][/B][COLOR=#1f497d]Destination.value=Target.value<o:p></o:p>[/COLOR][/FONT][/SIZE]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]            End If<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]        End If<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]    Next i<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=#1f497d][SIZE=3][FONT=Calibri]End Sub<o:p></o:p>[/FONT][/SIZE][/COLOR]

Posting the above code hopeful helps someone.

Biz