PDA

View Full Version : Modify Code



Emoncada
12-09-2008, 07:35 AM
MdMack was able to provide me with the following code. See previous question (http://vbaexpress.com/forum/showthread.php?t=24024)
It works perfectly for what I need.
I just have another spreadsheet that can use this, but needs to be modified.



Option Explicit
Sub DoStuff() Dim wb As Workbook
Dim ws As Worksheet
Dim ThsSht As Worksheet
Dim tgt As Range
Dim Target As Range
Dim MyPath As String


MyPath = "C:\Docu\" '<=====Change to suit


Application.ScreenUpdating = False
Set ThsSht = ActiveSheet
Set Target = ThsSht.Range("B6")
Set wb = Workbooks.Open(MyPath & "Asset Tracking.xlsx")
Set ws = wb.Sheets(ThsSht.Range("K5").Value)
Set tgt = ws.Columns(2).Find(Target.Value)
If tgt Is Nothing Then
Set tgt = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1)
tgt = Target.Value
End If
tgt.Offset(, 1) = ThsSht.Range("AA1").Value
tgt.Offset(, 2) = ThsSht.Range("V1").Value
wb.Close True
Set wb = Nothing
Application.ScreenUpdating = True
End Sub


What I need is instead of looking at just B6 I need it to grab a range from "E10:E1500" and I don't need to do a find(Target.Value) one because it will run and take a very long time.

Instead of
Set ws = wb.Sheets(ThsSht.Range("K5").Value)

I need it to look at Range ("D10:1500") and match with sheet.
If no match skip line.


Now instead of
tgt.Offset(, 1) = ThsSht.Range("AA1").Value
*This can be the same for all in the range it will be "D6"

Now for
tgt.Offset(, 2) = ThsSht.Range("V1").Value

I will need it to look at each row and grab the value in "O"
Hope that explains my new goal.

Thanks

Emoncada
12-09-2008, 01:25 PM
BUMP!

Emoncada
12-10-2008, 01:34 PM
BUMP!

mdmackillop
12-10-2008, 01:45 PM
I've read this three times now, but don't understand what is required. Please repost your workbook with suitable data.

Please also post your own coding, showing where the problems are.

Emoncada
12-11-2008, 07:26 AM
Modifing your previous code this is what I tried.

Sub Macro1()
Dim wb As Workbook
Dim ws As Worksheet
Dim ThsSht As Worksheet
Dim tgt As Range
Dim Target As Range
Dim MyPath As String

MyPath = "C:\Doc\" '<=====Change to suit

Application.ScreenUpdating = False
Set ThsSht = ActiveSheet
Set Target = ThsSht.Range("E10:E15")
Set wb = Workbooks.Open(MyPath & "Asset Tracking.xlsx")
Set ws = wb.Sheets(ThsSht.Range("E6").Value) '<<---I would like to have it look at Column D each line would have a Model and find sheet. If not found skip. (Here I used E6 which = "Master" added a Master worksheet to test.
Set tgt = ws.Columns(2).Find(Target.Value)
If tgt Is Nothing Then
Set tgt = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1)
tgt = Target.Value
End If
tgt.Offset(, 1) = ThsSht.Range("H10:H15").Value
tgt.Offset(, 2) = ThsSht.Range("O10:O15").Value
wb.Close True
Set wb = Nothing
Application.ScreenUpdating = True


End Sub

It works but just for one row.

How can I make this work.