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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,216,487
Messages
6,130,944
Members
449,608
Latest member
jacobmudombe

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