brentsimp

New Member
Joined
Jun 10, 2019
Messages
4
Hi All,

I'm relatively new to creating macros but have been working on the attached code for some time, unfortunately it ends with an error which i haven't been able to eliminate. The problem line is highlighted in red text. Any help on the associated error would be much appreciated.

Thanks in advance.
brentsimp





Code:
Sub Intertek_Assays()

Dim lCount As Long
Dim rFoundCell As Range
Dim CurrentSearch As String
Dim SearchResult As String
Dim inColumnsMax As Long
Dim outColumnsMax As Long
Dim HeaderColumn As Long           'Column  Reference for the LookupTable where the Headers are stored
Dim sheet_LookUP As String
Dim Concatenate As String
Dim HeaderCount As Long
Dim RawData As String
Dim OutPutData As String
Dim CurrentInput As Long
Dim UnMatchedColums As Long
Dim Niton_ReadingColumn As Integer
Dim PrefillColumn As Long
Dim rPrefillCell As Range
Dim inRowsMax As Long
Dim FinishedColumnOrder As Integer
Dim rCopy As Range
Dim rDestination As Range
Dim ThisBOOK As String




'Dim Sortorder As Address




'CurrentSearch = "Test"
'inColumnsMax = 100   'set to auto length later
'outColumnsMax = 100  'set to auto length later
HeaderColumn = 3
sheet_LookUP = "INTERTEK_LOOKUP"
'HeaderCount = 100 ' WorksheetFunction.Count(Columns(HeaderColum))






FinishedColumnOrder = 2
ActiveSheet.Name = "Raw_Data"
RawData = ActiveSheet.Name
Sheets.Add.Name = "Finished"
OutPutData = "Finished"


Worksheets(RawData).Activate
    Columns("B:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells(1, 1).Select


 
 
    
    Rows("8:8").Select 'Insert row and concatenate'
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A8").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=CONCATENATE(R[-7]C,""_"",R[-6]C,""_"",R[-4]C)"
    Range("A8").Select
    Selection.AutoFill Destination:=Range("A8:AZ8"), Type:=xlFillDefault
    Range("A8:AO8").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False




    Range("B5").Select 'add job number and dates'
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],52)"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "=LEFT(R[-4]C,15)"




    
    
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-3],34)"
    Range("D9").Select
    ActiveCell.FormulaR1C1 = "=LEFT(R[-4]C,10)"
    Columns("B:B").ColumnWidth = 18
    Columns("C:C").ColumnWidth = 12.71
    Columns("D:D").EntireColumn.AutoFit






   Range("B9:D9").Select 'fill down job number and dates'
    Application.CutCopyMode = False
    Selection.Copy
    Range("B9:D500").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


        
    Range("A8").Select 'add text'
    ActiveCell = "Sample_Number"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "job_no"
    Range("C8").Select
    ActiveCell.FormulaR1C1 = "date_received"
    Range("D8").Select
    ActiveCell.FormulaR1C1 = "date_reported"
    
    Rows("1:7").Select 'delete header rows'
    Selection.Delete Shift:=xlUp
    
    
        
    Workbooks("SIMP_ASSAY_MACRO.XLS").Worksheets(sheet_LookUP).Copy Before:=ActiveSheet


    
    Worksheets(sheet_LookUP).Activate
    Cells(1, HeaderColumn).Select
    Selection.End(xlDown).Select
    outColumnsMax = ActiveCell.Row - 1 'this finds how many total rows
    
    For HeaderCount = 1 To outColumnsMax
    Worksheets(OutPutData).Cells(1, HeaderCount).Value = Worksheets(sheet_LookUP).Cells(HeaderCount + 1, HeaderColumn).Value
    'Cells(1, lCount).Value = .Cells(lCount + 1, HeaderColumn).Value




     Next HeaderCount
     


                
Set rFoundCell = Worksheets(sheet_LookUP).Range("A1")
        'rFoundCell.Activate
    lCount = 1




        Do Until lCount = inColumnsMax   'There should be only one match.                    'WorksheetFunction.CountIf(Columns(1), "Cat")
            CurrentSearch = Worksheets(RawData).Cells(1, lCount).Value
            Set rFoundCell = Columns(1).find(What:=CurrentSearch, After:=Worksheets(sheet_LookUP).Range("A1"), _
                LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)
                
            If rFoundCell Is Nothing Then
                Worksheets(RawData).Columns(lCount).Interior.ColorIndex = 6  'color yellow to indicate no tansfer
                UnMatchedColums = UnMatchedColums + 1
                


                
            Else:


                'rFoundCell.Activate
                Set rCopy = Worksheets(RawData).Columns(lCount)
                rCopy.Copy
[COLOR=#ff0000]                Set rDestination = Worksheets(OutPutData).Columns(rFoundCell.Offset(0, 1).Value)[/COLOR]
                rDestination.PasteSpecial xlPasteValuesAndNumberFormats ' Operation:= _
                 '   xlNone , SkipBlanks:=False, Transpose:=False
                  '  Destination.:=Worksheets(OutPutData).Columns(rFoundCell.Offset(0, 1).Value)    'Copy and paste into appropriate column
                
                'Worksheets(RawData).Columns(lCount).Copy _
                '    Destination:=Worksheets(OutPutData).Columns(rFoundCell.Offset(0, 1).Value)    'Copy and paste into appropriate column
                Worksheets(OutPutData).Cells(1, (rFoundCell.Offset(0, 1).Value)).Value = rFoundCell.Offset(0, 2).Value    'rename column header
                rCopy.Interior.ColorIndex = 4 ' Worksheets(RawData).Columns(lCount).Interior.ColorIndex = 4   'color green to indicate all good and transfered
               ' rCopy.Cells.Activate
               
                End If


            If inRowsMax = 0 Then
            
            If rFoundCell.Offset(0, 2).Value Like "samp_id" Then
            'determine the number or total readings
                Worksheets(RawData).Activate
                Cells(1, lCount).Select
                Selection.End(xlDown).Select
                inRowsMax = ActiveCell.Row  'this finds how many total rows
                Worksheets(sheet_LookUP).Activate
                


    
            End If
            End If
            
        lCount = lCount + 1
        Loop




End Sub
 
Last edited by a moderator:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
What is the value in rFoundCell.Offset(0, 1)?
 
Upvote 0
I'm not sure whats going on to be honest, youre setting alot stuff equal to alot of other stuff, I saw you set rFoundCell twice so make sure you are expecting it to pull the right one and not the other one, But yeah sorry idk hard to tell with so much code and no sheet to match it with.
 
Last edited by a moderator:
Upvote 0
What is the value in rFoundCell.Offset(0, 1)?

Hi Mark,
Thanks for your prompt response. I should have provided further information in regard to what i am trying to achieve with the macro. The aim of the macro is to allow assay data received by a laboratory to be uploaded in a consistent format to our database. The assay laboratory data is often reported with elements in different column order which can be time consuming to manually re-organise the table. The macro uses a lookup table with defined column order and arranges the assay data to that defined column ordering. In regards to you question the macro looks for a particular column name in the lookup and then at the data in the assay report and copies the assay value when present. If the assay data for an element is missing then then the macro will jump to the next element in the lookup table.

The code has been edited from a similar macro from another laboratory, and i don't fully understand all the code etc.

Many Thanks

brentsimp
 
Upvote 0
In regards to you question the macro looks for a particular column name in the lookup and then at the data in the assay report and copies the assay value when present.

Actually what I was after is what is the value of rFoundCell.Offset(0, 1) when it errors.
Put the line in red below in the position shown and run the macro again, what does the message box show?

Code:
Set rCopy = Worksheets(RawData).Columns(lCount)
[COLOR="#FF0000"]MsgBox rFoundCell.Offset(0, 1).Value[/COLOR]
rCopy.Copy
Set rDestination = Worksheets(OutPutData).Columns(rFoundCell.Offset(0, 1).Value)

Also what does the error message state?
 
Upvote 0
Hi Simon,

The Visual Basic error is "13" type mismatch. Which may be a clue to the problem, the data contains numerical values and characters for below detection limit.

The code you provided in red has been run and generates numerical errors 1,2,3,4,8,5,6,7,9,11,12,13,14,15,16,17,18,20,21,22,23,24,25,26,27,28,29,48,30 etc i'm not entirely sure what the errors mean are they referring to columns or rows etc?
Why would they be partly out of order?

Many Thanks
brentsimp
 
Upvote 0
Hi Simon,
I should also mention that the macro effectively works despite the error message, the data is re-arranged in the correct order. Unfortunately i have another macro that i wish to run to convert the below detection limit values. Unfortunately i cant combine the two macros due to the error message being generated.

Many Thanks
brentsimp
 
Upvote 0
@Twollaston
Please do not quote entire posts (especially long ones), as it just clutters up the thread & makes it harder to follow.
Thanks
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top