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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

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:

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

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