Can This Be Simplified?

msfatman

Board Regular
Joined
Apr 10, 2008
Messages
110
Is it possible to make this code simpler?

Code:
Dim Found As Range
 
With Sheets("Viewer")
Set Found = Sheets("A").Range("CJ6:FK6").Find(what:=.Range("D3").Value)
If Not Found Is Nothing Then
Found.Offset(-2, 0).Value = .Range("G8").Value
Found.Offset(1, 0).Value = .Range("F8").Value
End If
 
Set Found = Sheets("B").Range("CJ6:FK6").Find(what:=.Range("D3").Value)
If Not Found Is Nothing Then
Found.Offset(-2, 0).Value = .Range("G9").Value
Found.Offset(1, 0).Value = .Range("F9").Value
End If
 
Set Found = Sheets("C").Range("CJ6:FK6").Find(what:=.Range("D3").Value)
If Not Found Is Nothing Then
Found.Offset(-2, 0).Value = .Range("G10").Value
Found.Offset(1, 0).Value = .Range("F10").Value
End If
 
Set Found = Sheets("D").Range("CJ6:FK6").Find(what:=.Range("D3").Value)
If Not Found Is Nothing Then
Found.Offset(-2, 0).Value = .Range("G11").Value
Found.Offset(1, 0).Value = .Range("F11").Value
End If
 
Set Found = Sheets("E").Range("CJ6:FK6").Find(what:=.Range("D3").Value)
If Not Found Is Nothing Then
Found.Offset(-2, 0).Value = .Range("G12").Value
Found.Offset(1, 0).Value = .Range("F12").Value
End If
 
Set Found = Sheets("F").Range("CJ6:FK6").Find(what:=.Range("D3").Value)
If Not Found Is Nothing Then
Found.Offset(-2, 0).Value = .Range("G13").Value
Found.Offset(1, 0).Value = .Range("F13").Value
End If
 
Set Found = Sheets("G").Range("CJ6:FK6").Find(what:=.Range("D3").Value)
If Not Found Is Nothing Then
Found.Offset(-2, 0).Value = .Range("G14").Value
Found.Offset(1, 0).Value = .Range("F14").Value
End If
End With
 
Last edited by a moderator:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
I apologize for not wanting to read through all of that, and yes, it can prolly be simplified, but what does it do exactly, if you don't mind writing it down for us?
 

Jacophile

Board Regular
Joined
Mar 8, 2009
Messages
51
Hi, you could try something like this:

Code:
Const SheetNames As String = "A,B,C,D"
Sub Test()
Dim s As Worksheet[INDENT] For Each s In ActiveWorkbook.Worksheets
[INDENT] If InStr(1, SheetNames, s.Name) Then
[INDENT]'do stuff with the sheet defendant on it's name
[/INDENT] [/INDENT][INDENT]End If
[/INDENT]Next s
[/INDENT]End Sub
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,506
Messages
5,837,740
Members
430,514
Latest member
Stanislav546564

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