VBA help required. - Value = Offset.value VS. Arrays

[cooper645]

My issue is this,

I wish to transpose information from one sheet to another, but re order that information.
The idea is to lookup information in a database, and display it clearly on a front page for the end user.


I enter a serial number into cell C14, click the shape/button to run the macro.

Currently the macro finds the serial number in the relevant sheet, and I then use that location (c) to enter the info on the current sheet by way of the following code.

Sheets("Cover Page").Range("C22").Value = c.Offset(0, 1).Value

I currently have about 20 lines as the above which show the end user the information in a more user friendly format.

It works ok but I can't help thinking there is a cleaner more efficient way to achieve my goal, possibly using arrays, but I'm not sure how to go about it. Hence the post on here. The entire code is below to help you try to understand my aim.

Any help you can provide or advice would be appreciated. I have not really worked with arrays before so please bear with me if this is a solution.

Terry

Option Explicit


Sub SerialSearch()


Dim SrchTerm As String, LastRow As Long, c As Range


SrchTerm = Sheets("Cover Page").Range("C14").Value
SrchTerm = WorksheetFunction.Trim(SrchTerm)
If SrchTerm = "" Then GoTo NoSerial


LastRow = Sheets("LE(A)2029" & Left(SrchTerm, 1)).Cells(Rows.Count, 1).End(xlUp).Row


With Sheets("LE(A)2029" & Left(SrchTerm, 1)).Range("A17:A" & LastRow)
    Set c = .Find(SrchTerm, LookIn:=xlValues)
        If Not c Is Nothing Then
            With Sheets("Cover Page")
                .Range("C22").Value = c.Offset(0, 1).Value 'Description
                .Range("C24").Value = c.Offset(0, 2).Value 'Serial Number
                .Range("C26").Value = c.Offset(0, 3).Value 'SWL/WLL
                .Range("C28").Value = c.Offset(0, 18).Value 'SN Location
                .Range("C30").Value = c.Offset(0, 12).Value 'stock No
                '.Range("C32").Value = c.Offset(0, 1).Value Need to lookup the stock no
                .Range("C34").Value = c.Offset(0, 16).Value 'DofM
                .Range("G22").Value = c.Offset(0, 5).Value 'Date of Last Insp
                .Range("G24").Value = c.Offset(0, 9).Value 'Inspection Type
                .Range("G26").Value = c.Offset(0, 6).Value 'Sentence
                .Range("G28").Value = c.Offset(0, 7).Value 'Restrictions
                .Range("G30").Value = c.Offset(0, 10).Value 'Remarks
                .Range("G32").Value = c.Offset(0, 8).Value 'Date of Next insp
                .Range("G34").Value = Round(c.Offset(0, 11).Value, 0) 'Days to next inspection
                .Range("K22").Value = c.Offset(0, 13).Value 'Equipment owner
                .Range("K24").Value = c.Offset(0, 4).Value 'Location/Dept
                .Range("K26").Value = c.Offset(0, 15).Value 'eqt type
                '.Range("K28").Value = c.Offset(0, 7).Value lookup from eqt type
                .Range("K30").Value = c.Offset(0, 17).Value 'Associated certs
                .Range("K32").Value = c.Offset(0, 8).Value 'Item on system?
            End With
        End If
End With


Exit Sub
UserInfo:
MsgBox "Please enter a valid 2029 number"
NoSerial:
MsgBox "You hav not entered a LE(A) 2029 number"
End Sub

 

[Fluff]

If you're happy having the info on consecutive rows, you could use this for the Col C values

   Range("C22:C27").Value = Application.Transpose(Application.Index(c.Resize(, 18).Value, 1, Array(1, 2, 3, 18, 12, 16)))

 

[cooper645]

Thanks Fluff, will look into this and figure it over the weekend. I think I get the idea of it. Will also try to use a blank column to fill the alternate rows.

 

[Fluff]

If the 19th column from "c" was blank, you could do

Range("C22:C34").Value = Application.Transpose(Application.Index(c.Resize(, 19).Value, 1, Array(1, 19, 2, 19, 3, 19, 18, 19, 12, 19, 19, 19, 16)))

 

[cooper645]

If the 19th column from "c" was blank, you could do

Range("C22:C34").Value = Application.Transpose(Application.Index(c.Resize(, 19).Value, 1, Array(1, 19, 2, 19, 3, 19, 18, 19, 12, 19, 19, 19, 16)))

That was exactly what I was thinking of doing. Thanks again Fluff. I hope this should drastically speed things up. I need to start working with arrays a lot more to increase efficiency and decrease typing.