Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod crash desktop dns driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop mac malware memory monitor motherboard network not working printer problem ram registry repair router slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Excel Cross-Referencing issue

Reply  
Thread Tools
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
17-Nov-2009, 06:42 PM #31
Btw, the entire code in the module should look like this...
Code:
Option Explicit

Sub CheckForReferences(rCell As Range)
    
    Dim ws As Worksheet, i As Long
    Dim rLook As Range, rFind As Range, rRef As Range
    Dim aVals() As String, sType As String
    Dim sTemp As String
    
    Set ws = rCell.Parent
    
    '## Should either be GPB or PQS
    sType = rCell.Offset(0, 1).Value
    
    sTemp = Trim(Trim(rCell.Offset(0, 3).Value) & " " & Trim(rCell.Offset(0, 2).Value))
    aVals = Split(sTemp, " ")
    Set rLook = ws.Columns(1)
    
    If UBound(aVals) < 0 Then Exit Sub
    
    Call TOGGLEEVENTS(False)
    
    For i = LBound(aVals) To UBound(aVals)
        Set rFind = rLook.Find(What:=aVals(i), LookAt:=xlWhole, MatchCase:=True)
        If Not rFind Is Nothing Then
            If rFind.Value = rCell.Value Then GoTo SkipOnce
            Select Case sType
            Case "GPB"
                Set rRef = rFind.Offset(0, 3)
            Case "PQS"
                Set rRef = rFind.Offset(0, 2)
            End Select
            If Len(rRef.Value) <> 0 Then
                If InStr(1, rRef.Value, rCell.Value, vbTextCompare) = 0 Then
                    rRef.Value = rRef.Value & " " & rCell.Value ' aVals(i)
                End If
            Else
                rRef.Value = rCell.Value 'aVals(i)
            End If
SkipOnce:
        End If
    Next i
    
    Call TOGGLEEVENTS(True)
    
End Sub

Public Sub TOGGLEEVENTS(blnState As Boolean)
'Originally written by Zack Barresse
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub

Sub TestMeNowPlzzz()
    Call CheckForReferences(Cells(ActiveCell.Row, 1))
End Sub
ab11viva's Avatar
Computer Specs
Junior Member with 17 posts.
 
Join Date: Oct 2009
Experience: Intermediate
18-Nov-2009, 05:38 PM #32
Beautiful... I think we've got it.

I'm not even sure if this is desired or not but would it be easy to edit the code to remove references if one was deleted. (i.e. basically the opposite of what you just built) If it's easy, great. If not, you've spent enough time on this and I appreciate your help.

Thanks
Adam
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
19-Nov-2009, 07:18 PM #33
Well we can certainly add that if you want. It would pretty much require looping through the whole workbook every time though. Are the references going to be deleted very often? Is there a time when they would be? Something we could look for? Would you like a 'clean up tool' so-to-speak, something you could click and it would look up and clean missing references? I'm a little worried about performance if we add that code to what we have, on a change event.
ab11viva's Avatar
Computer Specs
Junior Member with 17 posts.
 
Join Date: Oct 2009
Experience: Intermediate
20-Nov-2009, 03:19 PM #34
Zack,

I think a cleanup tool would be a great idea so that it could be run "on-demand".

You're right that this is not a regular enough occurence for it to be set up to constantly monitor.

Thanks
Adam
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
24-Nov-2009, 07:33 PM #35
So, I must be confusing myself here. Can you explain to me the logic this should follow, to find these items to clean up?
Reply

Tags
excel

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 02:49 AM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.