Consulting

Results 1 to 5 of 5

Thread: Modify Code

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location

    Modify Code

    MdMack was able to provide me with the following code. See previous question
    It works perfectly for what I need.
    I just have another spreadsheet that can use this, but needs to be modified.

    [vba]
    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
    [/vba]

    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
    [vba]Set ws = wb.Sheets(ThsSht.Range("K5").Value) [/vba]

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


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

    Now for
    [vba]tgt.Offset(, 2) = ThsSht.Range("V1").Value [/vba]

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

    Thanks
    Last edited by mdmackillop; 12-09-2008 at 10:56 AM. Reason: Link added

  2. #2
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    BUMP!

  3. #3
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    BUMP!

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Expert
    Joined
    Apr 2007
    Location
    Orlando, FL
    Posts
    751
    Location
    Modifing your previous code this is what I tried.

    [VBA]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[/VBA]

    It works but just for one row.

    How can I make this work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •