VBA for hlookup, copy and paste.

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
53
Hi all

Please, I am in need of some help with my code, I have recorded some code and have the basics which does work, once!
I have done this with excel - Hlookup and it works well but each time the text changes (daily) on the Master sheet all the other sheets change. That's why I thought vba with a button command would work better.

Basically I would like the vba to find the last text in sheet "Furnace 1", column A, find the matching text in sheet "Master" (could be any where from E5:N24). Copy the cell below the matching text in sheet "Master"and paste it into the cell next to the original text in sheet "Furnace 1", column B.
Then repeat this for the other sheet "Furnace 2", column A up until "Furnace 10" if there are any occurrences, if not then nothing to copy.

Many thanks for your help.

Below is my code.
Sub Macro1()
'
' Macro1 Macro
'
Sheets("FURNACE 1").Select
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lastRow).Select
Cells.Find(What:="h1831", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Sheets("Master").Select
Cells.FindNext(After:=ActiveCell).Activate
Range("E5").Select
Selection.Copy
Sheets("FURNACE 1").Select
Range("B4").Select
ActiveSheet.Paste
End Sub
 

Attachments

  • Capture.PNG
    Capture.PNG
    51 KB · Views: 5

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
@pete4monc Can you please confirm or otherwise:

10 sheets for Furnaces 1- 10
Range E5:N6 in Master sheet relates to Furnace 1. Range E7:N8 to Furnace 2. etc*.
Code to find last Heat No. entered in column A of each Furnace sheet
Find the corresponding Alloy from the appropriate Master sheet range*
Paste the Alloy to column B of the Furnace sheet. Or Blank if nothing found.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
Assuming that my above assumptions are correct then, the following may well be worth a test.

VBA Code:
Sub Furnace_Update()

For F = 1 To 10   'Furnace' Sheet suffix

    HtRow = 3 + 2 * F    'Heat No row
    AlRow = HtRow + 1    'Alloy row
    Furnace = "Furnace " & F   ' Furnace sheet name  
    On Error GoTo DuffSheetName   'To redirect  if missing or bad sheet name
   
        With Sheets(Furnace)  'With current Furnace sheet of the loop
            LastRow = .Cells(Rows.Count, "A").End(xlUp).Row   'Last Heat No Row           
            ' Enter Index / Match formula in Furnace sheet col B
            .Range("B" & LastRow).Formula = "=IFERROR(INDEX(Master!E" & AlRow & ":N" & AlRow & ",1,MATCH(A" & LastRow & ",Master!E" & HtRow & ":N" & HtRow & ",0)),"""")"
            .Range("B" & LastRow).Value = .Range("B" & LastRow).Value  'convert the formula to it's resultant value
        End With

    DuffSheetName:    'Sheet name errors re-directed to here
    Resume Next       'Ignore the error
Next F      ' Loop for next Furnace sheet suffix
On Error GoTo 0    'Re-set default error handling
End Sub

Hope that helps.
 

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
53
Hi Snakehips
@pete4monc Can you please confirm or otherwise:

10 sheets for Furnaces 1- 10
Range E5:N6 in Master sheet relates to Furnace 1. Range E7:N8 to Furnace 2. etc*.
Code to find last Heat No. entered in column A of each Furnace sheet
Find the corresponding Alloy from the appropriate Master sheet range*
Paste the Alloy to column B of the Furnace sheet. Or Blank if nothing found.

Hi Snakehips - Thanks so much for your help.

Master sheet changes daily so the A column in the Furnace sheets needs to search for the Heat number H1831 on the Master sheet. Finds it and copies the alloy (HR5) to the Furnace 1 sheet and paste it next to the corresponding heat number H1831 in column B. Then repeats to find the other matching heat numbers and alloys. In total there are 11 sheets, Master, Furnace 1, Furnace 2 etc etc. up to Furnace 10.
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    35.2 KB · Views: 4
  • Capture2.PNG
    Capture2.PNG
    16.3 KB · Views: 4

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Please bear in mind that we don't share your intimate involvement in the subject so, what is as clear as day to you can be as clear as mud to the rest of us.
So:
Master sheet changes daily.
The numbers down left side of Master sheet do not relate to a Furnace number? So Furnace 1 Heat No match could be found in section 8 ?
As per your original post you are looking to match the last text in Column A, of each Furnace sheet, with a Heat No in the Master sheet and return the corresponding Alloy ?
Code to loop through 10 Furnace sheets and just check each last row?
 

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
53
Please bear in mind that we don't share your intimate involvement in the subject so, what is as clear as day to you can be as clear as mud to the rest of us.
So:
Master sheet changes daily.
The numbers down left side of Master sheet do not relate to a Furnace number? So Furnace 1 Heat No match could be found in section 8 ?
As per your original post you are looking to match the last text in Column A, of each Furnace sheet, with a Heat No in the Master sheet and return the corresponding Alloy ?
Code to loop through 10 Furnace sheets and just check each last row?

@Snakehips
The numbers down left side of Master sheet do not relate to a Furnace number? Well spotted, Yes they do.
So Furnace 1 Heat No match could be found in section 8 ? No, Furnace ! heat will only be found in E2:K2
As per your original post you are looking to match the last text in Column A, of each Furnace sheet, with a Heat No in the Master sheet and return the corresponding Alloy ?
Code to loop through 10 Furnace sheets and just check each last row? Yes
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

@pete4monc
In that case, then, my original code should pretty much be ok?
Have you actually tried it???

I got the impression from your original post that the Master Heat No data was potentially in columns E : N
If as per your latest post, it is only columns E : K then edit the code row as per below, where i have changes two instances of N to K

VBA Code:
.Range("B" & LastRow).Formula = "=IFERROR(INDEX(Master!E" & AlRow & ":K" & AlRow & ",1,MATCH(A" & LastRow & ",Master!E" & HtRow & ":K" & HtRow & ",0)),"""")"
 

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
53
@Snakehips vba works a treat. I found out why it wasn't working originally, the Row headings had : after the text....lol ...Thanks so much, I really appreciate the help.

How could I get the vba to copy multiple heats on the same day from the same furnace?
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    22.3 KB · Views: 1
  • Capture2.PNG
    Capture2.PNG
    14.9 KB · Views: 1

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
Pleased it helped.

Try this for multiple Heat Nos

VBA Code:
Sub Furnace_Update()

For F = 1 To 10   'Furnace' Sheet suffix

    HtRow = 3 + 2 * F    'Heat No row
    AlRow = HtRow + 1    'Alloy row
    Furnace = "Furnace " & F   ' Furnace sheet name
    
    On Error Resume Next   'To redirect  if missing or bad sheet name
    
        With Sheets(Furnace)  'With current Furnace sheet of the loop
            If Err.Number > 0 Then
            Err.Clear
            GoTo DuffSheetName
            End If
            FirstRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1  'First blank Heat No Row
            LastRow = .Cells(Rows.Count, "A").End(xlUp).Row   'Last Heat No Row
            
            ' Enter Index / Match formula in Furnace sheet col B
            .Range("B" & FirstRow & ":B" & LastRow).Formula = "=IFERROR(INDEX(Master!E$" & AlRow & ":N$" & AlRow & ",1,MATCH(A" & FirstRow & ",Master!E$" & HtRow & ":N$" & HtRow & ",0)),"""") "
            .Range("B" & FirstRow & ":B" & LastRow).Value = .Range("B" & FirstRow & ":B" & LastRow).Value  'convert the formula to it's resultant value
        End With

DuffSheetName:   'Sheet name errors re-directed to here

Next F      ' Loop for next Furnace sheet suffix

On Error GoTo 0    'Re-set default error handling
End Sub

Edit the N to K again if appropriate.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,343
Members
417,021
Latest member
moon miner

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
Top