Hi Tony

It would be great if you could post a sample file with as much data as possible and the expected results for us to see.

It will be easier for us to help you to find a solution.

Sunny

Hi Sunny,

2nd attempt at a reply - working remotely and trying to load it I was getting JSON parse errors when I tried to upload, so resorted to emailing it to myself to do locally!

The attached file is a short extract showing 2 blocks in one building (Butler B and C). There are extra columns to the right which are not relevant to the coding (just test results etc.). I have the coding sorted for Column C - Zone Code, and that is working well, as this will be the same for all blocks within the data set. I use an input form to ask for the code (01D in this instance), and fill down to the end of Column C. The end is determined by a global variable FinalRow, which I can use for the range, the sort sub routine, etc.

What I haven't been able to find any reference for is how to look through the data in Column E and create a range for ButlerB (in this instance), match that range in in Column D and use an input form for 26B etc. then repeat for the range of ButlerC. Some of the sheets I get will not have blocks, others will have several.

Repeat this exercise from Column G to get the various floor and Flat sets. Unfortunately the data from the test engineers isn't consistent, with different ones using different forms of coding, and also as a result of the different physical coding within the different buildings across campus here. As you can imagine, a university with buildings spanning 130 years of building, plus some older ones purchased, there is no consistent numbering on the doors!

Whether I can repeat the exercise for room numbers, by doing a further sort on the end characters of the Floor Description, is open to debate - at least the actual code for the rooms which I need to go into the system is in a set format, so all rooms which are say, room 9 on any floor in any building will have a code of 009Z, etc. However, it doesn't help when different engineers record the data in different forms e.g. in one area, one team is using E-4B-05 and a colleague has used E4B06. (Note to self - next year, ask the contractor to use a consistent format!!)

Hope this all helps, and any ideas welcomed!

Thanks,

Tony.

Hi Tony

I am just making a guess here based on your data given. Correct me if I am wrong.

1) Columns E (Building Desc) and G (Floor Desc) are keyed in by others (colleagues/engineers??).

2) You want to fill in column D (Building Code) so that all BUTLER B is 26B and BUTLER C is 26C (for example) using an input form.

Why use an input form? What kind of input form is that? Form or ActiveX controls?

3) You want to fill in Column H (Room Code) based on what is entered in column G (Floor Desc)

OK lets start with Column H (Room Code). You can use the formula ="0" & RIGHT(G2,2) & "Z" to get the Room Code.

For Column D (Buildnig Code), you can use ="26" & RIGHT(E2,1) to get the Building Desc.

Not too sure if this is what you wanted.

Hi Sunny,

Trying to get the various bits of data in - Column C has 16 options - but each building that has been tested (for portable appliance electrical safety) will only need one input. I'm using

 myInput = InputBox("Zone")
            Range("C2:C" & FinalRow).Value = myInput

so I provide the zone code and it fills it.

Column D needs to translate the building and block from the text name which the engineers have entered. So, in the sample, it is Wedderburn B and C, so requires 2 codes for the different sections. Some reports may have up to 20 different building codes and blocks in them, some will be a single code, so there is no pattern to it, which is why I was looking to automate the fill, but not the code selection. Similarly with floor levels - some buildings are a single floor, others have a mix of floors, flats (e.g. 01A, 01B etc.) so again each building will be different. For rooms, at least the code for each of these is pretty consistent in the system (but not on the doors!) so identifying all 'room 9' locations would let me fill with the code '009Z' across the sheet, although there are a few exceptions where the actual location is a sub-store or something so has a suffix of A instead of Z.

Hence, with the way the data is presented, there is still a lot of manual checks which need to go in, but at least automating the fill would still save a lot of time. Some reports just put a single room number like 1, 2 etc (especially if it is a single floor) but the multi-floor buildings naturally add complexity. This is why I was looking to use a part-automated process, where I control the inputs, and then automate the fill process.

At least for single floor single buildings I can loop the above code for the first 3 codes, but splitting them is the challenge.

Hope that makes sense - doing this across upwards of 50 buildings is slow going!

Thanks again,

Tony.

Hi Tony

I am unable to visualize what you want.I hope this is the code you are looking for, otherwise I hope other members can assist.

The codes are based on your workbook. Note that I have use TRIM in the code as BUTLER C has a trailing space in your data.

It will ask you for the Building Desc and then the Building Code to fill in column D.

Sub FillCode()

Dim BuildingDesc As String, BuildingCode As String
Dim r As Long, myRange As Range, cell As Range

r = Cells(3, 5).End(xlDown).Row
Set myRange = Range("E3:E" & r)

BuildingDesc = InputBox("Building Desc")
BuildingCode = InputBox("Building Code")

For Each cell In myRange
If UCase(Trim(cell)) = UCase(BuildingDesc) Then
cell.Offset(, -1) = BuildingCode
End If
Next
End Sub

Thanks Sunny,

It wasn't quite what I was after but I can see how that is working, using an input for the cell comparison and the second input for the cell fill.

What I was looking for (and I'm beginning to think I am hoping for too much) is to read the first value in the column, and create a range for all of that value, then use that for the input into the code column. Then read the next value down the column and create a range for that (assuming that the column is sorted so all values are together). Repeat until the final row is reached. That way, only 1 input is needed for each value.

That process could then potentially be re-used for the floor codes and potentially for the room codes, with some adjustment to select the relevant code format (which with the various forms of recordintg these from the engineers may make is more work than it saves!)

I'll give your routine a try and see how it looks, and appreciate your thoughts on this.

Tony.

Hi Tony

If your value is fixed, you could be better-off using a VLOOKUP on the Building Desc for the Building Code.

Referring to your quote "That way, only 1 input is needed for each value", my codes too only require one input for each value. You don't even have to sort your column to get the result . 

I think you want to give a unique range name for all cells with the same value i.e. all cells with the value "BLOCK B" is given a range name BLOCK-B (for example) and so on. Am I right? Your final objective is still to fill in the Building Code (or some other codes in another column).

You may be able to tweak my codes to do the same for the other columns.

Sunny

Hi Sunny,

I've had a play with your code and written something that is doing roughly what I was thinking of for he Building Codes. Hopefully not too messy, as I'm still very much a novice at this VBA stuff!! Running your code on a different sheet, calling it from the main routine to do the sort and the zone code, I found that it wasn't looping to pick up the other buildings for some reason.

I already have FinalRow defined as a public variable, so I'm using that to define the bottom of the range. I've come up with the following routine ...

Sub FillCode2()
'Step through each cell in a sorted column E "myRange" and compare each active cell with the one above
'If the ActiveCell is the same as the cell above, apply the Building Code to the cell in Column D
'If the cell above is different, use an InputForm to request the Building Code

Dim MyBuildingCode As String

    Set myRange = Range("E2:E" & FinalRow)
        
    For Each cell In myRange
    
        If cell <> cell.Offset(-1, 0) Then
            MyBuildingCode = InputBox("Building Code")
            cell.Offset(0, -1) = MyBuildingCode
                
        Else: cell.Offset(0, -1) = MyBuildingCode
        
        End If
        
    Next
    
        
End Sub

This one runs through, asking for the input for the first cell in Row 2 and applying it to Column D, then continuing down until it reaches the second building and stops for the next input. Repeat until the third input, and again for the fourth. Stops at the bottome of the column.

So that's 2 columns filling nicely - but I think that doing floors and room numbers may be somewhat more challenging, especially given the variations in recording these in the sheets! Still, gives me something meaty to chew on again 🙂

Once again, Many thanks for your help, ideas and support. I will probably be back soon for ideas on the other two columns LOL.

Tony.

Wow Tony !!! I'm impressed with your idea.Not bad for a VBA "novice". 

You could consider add this line of code to get the FinalRow instead of defining it as a public variable.

FinalRow = Cells(2, 5).End(xlDown).Row

Set myRange = Range("E2:E" & FinalRow)

Good luck on your experiment 

Thanks Sunny - I've got a slight variation on setting the 'FinalRow' variable ... FinalRow = Cells(Rows.Count, 5).End(xlUp).Row. Before I set it as a public variable, I found that calling the Sort sub Routine complained that it didn't recognise it, so making it Public solved that hurdle.

This is based on a line in a book on Excel 2007 VBA (all of the later stuff in the library was taken LOL - but it still works!)

I've done various bits of coding in various languages in the past (starting with Basic in the 1980's, and even some BBC Micro Machine Code - that was scary!) plus a bit in Java, although my favourite was SmallTalk - object oriented programming, designed for kids so adults could understand it hehe. So I understand the basics of loops etc. but VBA does seem to be a different world.

Your experience giving me the idea of using 'For Each cell In myRange' then set me off with the comparitive data to look for the next point of input - so a lot of the credit goes to you for triggering the brain cell! (Still doesn't allow for the originating engineer though - I tried it on another sheet and all seemed OK until I found that they had omitted the Block suffix on a load of them, so I was getting the wrong code! Ho Hum.)

Anyway, many thanks again - it's the second time you've been of massive help, and I do appreciate it.

As well as looking at the other two columns I want to add a quick check at the beginning to see if Row 2 is blank and if so delete it, but hopefully that will slot in nicely at the top of the main routine.

Tony.

Glad I was able to assist you.

Cheers 

Sunny