Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change
Any help on this? I'll 'scale' it up for my own needs later.
At the moment I have a macro for each cell in the b column of section one comparing to cells in b column of section two that uses lots of 'If', 'Then' and 'ElseIf' fired by a worksheet change in section one. Coding to make this work is huge, inefficient and prone to typo errors. There are two sections on worksheet. Section one range is A10:E20 Section two range is A30:E40 Can you explain how to use loops, variables and worksheet change to make the following happen? If there is a match between the values in the B column in both sections then the values from from corresponding rows are are carried down. Examples: If the value in B11= value in B34 then A34 = A11, C34 = C11 and E34 = C11 If the value in B15= value in B30 then A30 = A15, C30 = C15 and E34= C15 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change
Have a look at the following in the sheet code.
Option Explicit Private Sub Checkline(rCell As Range) Dim vSRow As Variant Dim lDRow As Variant lDRow = rCell.Row On Error Resume Next vSRow = "Error" vSRow = Application.WorksheetFunction.Match(rCell.Value, Me.Range("B10:B20"), 0) If IsNumeric(vSRow) Then ' found a match vSRow = vSRow + 9 ' its an index starting at 1 so add 9 to give 10-20 Cells(lDRow, "A") = Cells(vSRow, "A") Cells(lDRow, "C") = Cells(vSRow, "C") Cells(lDRow, "E") = Cells(vSRow, "C") ' not E???? Else ' no mach then clear Cells(lDRow, "A") = "" Cells(lDRow, "C") = "" Cells(lDRow, "E") = "" End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim lDRow As Variant If Intersect(Target, Me.Range("B10:B20", "B30:B40")) Is Nothing Then Exit Sub End If Application.EnableEvents = False ' disable events If Target.Row < 30 Then ' need to do all the cells For lDRow = 30 To 40 Checkline Cells(lDRow, "B") Next lDRow Else ' only check the one row Checkline Target ' use the targer in this case End If Application.EnableEvents = True End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "londoned" wrote: Any help on this? I'll 'scale' it up for my own needs later. At the moment I have a macro for each cell in the b column of section one comparing to cells in b column of section two that uses lots of 'If', 'Then' and 'ElseIf' fired by a worksheet change in section one. Coding to make this work is huge, inefficient and prone to typo errors. There are two sections on worksheet. Section one range is A10:E20 Section two range is A30:E40 Can you explain how to use loops, variables and worksheet change to make the following happen? If there is a match between the values in the B column in both sections then the values from from corresponding rows are are carried down. Examples: If the value in B11= value in B34 then A34 = A11, C34 = C11 and E34 = C11 If the value in B15= value in B30 then A30 = A15, C30 = C15 and E34= C15 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change the pivot chart automaticaly as values in the worksheet change | Excel Worksheet Functions | |||
change formula in a shared worksheet without losing change history | Excel Worksheet Functions | |||
Change in one Worksheet Activates Another Worksheet Change | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |