Moving Range, Find & Change Value

[LordDragon]

Greetings Excel Gurus,


I have another problem I need some help with please.


I'm running into a conflict with my project requirements. I have to be able to sort each system part list by any column on the sheet, and by color.
I have all those working, except the by color one.
But the problem I'm asking about here is the conflict which says that I need to "lock" certain items based on the project criteria.


For example: On the "AutoDriller" page, there are a few items that are either 110V or 220V. Which means that they have a different part number depending on the voltage the system runs on.


If the Project criteria says that the system is 110V, then I need the 220V item locked so it cannot accidentally be selected.


Before adding the "Sort" feature, this was working fine. I was locking and hiding the entire row that contained the item that shouldn't be ordered.


Now, I can't do that because once the sheet is sorted by something other than the default, whatever was in the hidden rows gets moved and then whatever is in that row now is not visible.


I removed the hidden rows part and attemepted to change the name of the item in the cell by declaring a name for that cell and then changing the item in the cell to match the item that should be used.


This also didn't work, because the cell name remained where it was set instead of following the item.


For example: on the AutoDriller page again. The items in question are the Control Box (110V) and (220V). They are on Rows 3 & 4 respectivley. I attempted to name the cell in B3 "ADRConBox" and the matching part number cell (D3) "ADRConBoxPN" I then had the following code:

    If ActiveWorkbook.Worksheets("Project Info").VoltageBox.Value = strVolt1 Then
        With ActiveWorkbook.Worksheets("AutoDriller")
            .Range("ADRConBox").Value = "ADR Control Box (110V)"
            .Range("ADRConBoxPN").Value = "ADR030"
        End With

This does change the current item in cells B3 and D3 to the desired value. However, if the sheet is not default sorted and is sorted by say, part number. Then the item in those cells is no longer the desired item and therefore should not be changed.


I need a way to "find" the part number in column D and then change the rest of the values on that row to match the desired item.


I've looked into VLOOKUP, but it states that the item being searched for has to be in the first column and I have not found a way to change that.


I have tried looking into the .Find feature, but I haven't found much about it, yet.


Any help or suggestions would be appreciated.

I have attached a copy of my workbook in case that will help. If you use the workbook, I have added two tabs to the ribbon that have the controls for most my macros.


Thanks.

[p45cal]

I've looked into VLOOKUP, but it states that the item being searched for has to be in the first column and I have not found a way to change that.

I've not got time right now to look at the rest of your problem but I picked up on the above; have a look at index and match:
instead of
=VLOOKUP("dfg3",B11:E20,4,FALSE)
look at:
=INDEX(E11:E20,MATCH("dfg3",B11:B20,0))

The above two formulae are equivalent, but in the second, the two vertical ranges can be anywhere (not even on the same rows nor even in the same orientation).

[SamT]

Sub HideVoltage(Sht As Worksheet, ColAddress As String, Voltage As String)
'Sht = Sheets("sheet1"), ColAddress = "C:C" or "$C:$C" or RangeName

Dim LastRow As Long
Dim C As Long
With Sht.Range(ColAddress)
  LastRow = .Cells(Cells.Count).End(xlUp).Row
  
  For C = LastRow To 1 Step -1 'Assumes Voltage(value) never appears in headers
    If InStr(.Cells(C), Voltage) > 0 Then _
      .Cells(C).EntireRow.Hidden = True
  Next C
End With
End Sub

[LordDragon]

p45cal,

look at:
=INDEX(E11:E20,MATCH("dfg3",B11:B20,0))

Thanks for the suggestion. I'm looking into it, but I haven't found anything that explains how to apply the INDEX/MATCH feature via VBA. If you could explain a little more in detail, when you have time, I'd appreciate it.


SamT,


It kind of looks like your suggestion is trying to hide the last row of my table. I'm not sure how this helps with my problem. I'm trying to find a part number and make sure the rest of the row that number is on is correct.


Bascially, if the system is running on 110V, then the control box with part number ADR030 should be used. If the system is running on 220V, then the control box with part number ADR034 should be used.


What I want to do is check the list for both part numbers (since only one should exist at a time), if the system is supposed to be 110V and part number ADR030 exists, then on that row, cell A should be set to "Yes", cell B should remain "ADR Control Box (110V)", cell C should remain "ADR030", cell D should be set to 1. However, if part number ADR034 exists, then all the cells on that row should be set to the proper vaule: A = Yes, B = ADR Control Box (110V), C = ADR030, D = 1.


I'll need to be able to check two other items on the list for the proper part number also. Then make the appropriate changes for each. Then if the system is supposed to be 220V, I need to change the appropriate rows to the correct values.


Since the rows could be sorted differently than my "default" sort, I need to be able to find the part to change before the changes take place.


Thanks again.

[SamT]

It sounds like what is already in the cells in not important, but what is supposed to be in the cells is.

If SystemV = 220V then
PartNo = "ADR034"
PartName = "ADR Control Box (220V)"
End If
Dim Found As Range
Using Find...FindNext loop
Set Found = Find ("ADR Control Box")
Found = PartName
Found.Offset(0, -1) = "Yes"
Found.Offset(0, 1) = PartNo
Found.Offset(0, 2) = "D"
FindNext
Next Sheet

[p45cal]

Now that I've looked at your workbook I can see what you're trying to do.
In the attached, I have made changes to the Worksheet_Activate sub of the Sheet AutoDriller.
At the moment I have only looked at the voltages side of things for that sheet - nothing else.
So test it only for voltages before and after sorting on that sheet only.
Look at comments in the code. Step through the code, enable/disable some commented-out lines as you please.

If it's more or less OK, I see it being converted to a more genaralised sub that you can put in a standard code-module and call with arguments from the greater proportion of sheets when they're activated. You would pass arguments such as the sheet, the columns containing the part numbers, the columns needing adjustment, the arrays of part numbers that need attention, perhaps some default values etc. This would make the task of maintaining/adjusting multiple sheets' _Activate routines much easier, quicker and more robust, reducing it to just adding new part numbers to the arrays when new parts are introduced.

[p45cal]

Obviously no earthly good whatsoever.

[LordDragon]

Sorry for the delay in responding to this. I have been busy with other things and was told to put this on hold for now.

Hopefully I'll have some time to look at this further in a week or two.

Thanks.