+ Reply to Thread
Results 1 to 8 of 8

Macro fill cell based on value from another sheet - columns change

Hybrid View

tcy1227 Macro fill cell based on... 07-25-2012, 12:59 PM
patel45 Re: Macro fill cell based on... 07-25-2012, 03:14 PM
tcy1227 Re: Macro fill cell based on... 07-25-2012, 07:13 PM
patel45 Re: Macro fill cell based on... 07-26-2012, 02:01 AM
tcy1227 Re: Macro fill cell based on... 07-26-2012, 09:27 AM
tcy1227 Re: Macro fill cell based on... 08-01-2012, 06:08 PM
patel45 Re: Macro fill cell based on... 08-02-2012, 01:37 AM
Cutter Re: Macro fill cell based on... 08-02-2012, 07:01 AM
  1. #1
    Registered User
    Join Date
    10-31-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Macro fill cell based on value from another sheet - columns change

    Regarding this macro - I have the following which updates pretty well. But I do have one more question:


    Dim lastrow As Long, lastrowi As Long
    Application.ScreenUpdating = False
    Set shvalues = Sheets("Values")
    lastrow = shvalues.Cells(shvalues.Rows.Count, "B").End(xlUp).Row
    For j = 4 To lastrow
      If shvalues.Cells(j, 6) <> "" Then
        findkey = shvalues.Cells(j, 2).Text & shvalues.Cells(j, 3).Text & shvalues.Cells(j, 4).Text
        For i = 2 To Worksheets.Count
          With Worksheets(i)
            lastrowi = .Cells(.Rows.Count, "B").End(xlUp).Row
            For ii = 1 To lastrowi
              keyii = .Cells(ii, 2).Text & .Cells(ii, 3).Text & .Cells(ii, 4).Text
              If keyii = findkey Then
                .Cells(ii, 6) = shvalues.Cells(j, 6)
              End If
            Next ii
          End With
        Next i
      End If
    Next j
    Application.ScreenUpdating = True
    
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("F5:F500")) Is Nothing Then UpdatePrice
    End Sub

    My question is if I want to expand the number of columns that will update, how do I do that. For example, if I want the macro to update based on changes to column s F G H, what changes do I make in the code?
    Last edited by Cutter; 08-02-2012 at 07:00 AM. Reason: Added code tags

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1