Auto fill formula VBA with constant rule

[garypea123]

Good Morning,

New to VBA but becoming more and more accomplished with your guys help

So here is my question regarding my next script.

From Column E this is a data export (so not changeable from report). The lines will change each hour potentially, but the constant:

The word ordered in Column F, and a name in Column E (to the left).

Now what I want in column B which:

Will return a blank if the adjacent result in column F is ordered.
Will return the name of the person in Column E if (to the left of ordered) until the next ordered in the further down the column. And then repeat until the end of the data in Column F.

I have provided an example of what it should look like.

I hope this makes sense.

 

[eirikdaude]

How do you know which cells in column E contains names? Are all the other cells numbers, are those the only colored cells, does your macro fill them in for you, etc?

 

[garypea123]

The names will only be to the left when the is the word ordered in Column E. The other cells in column E contain other data (not numbers) but it should pick up the name next to ordered. Until the next ordered (in which there will be a different name to the left).

Hopefully that makes sense. The colouring is just something I added to highlight what is needed.

From column E this is only a data export so there is no formula or manipulation. But what I try to create from B to D is a formula I can use so I can SUMIF / SUMPRODUCT / MATCH&INDEX etc. But to do this, I need to populate the name that is the left of each "ordered' until the next "ordered" when the name changes.

Thanks,
Gary

 

[garypea123]

At the minute the info in Column B is manually added, but I try to automate this if possible. But its tricky to come up with the correct logic and script.

 

[eirikdaude]

In that case I think something like this would do the trick for you. Is it parseable for you, or do you want some explanation on some of the steps?

Option Explicit

Sub test()
    Dim c As Range, r As Range
    Dim name As String
    Const to_find As String = "ordered"
    
    Set r = Sheet2.Range("F2:F" & Sheet2.Range("F" & Sheet2.Rows.Count).End(xlUp).Row)
    Debug.Print r.Address
    
    For Each c In r
        If StrConv(c, vbLowerCase) = to_find Then
            name = c.Offset(0, -1)
        Else
            c.Offset(0, -4) = name
        End If
    Next c
    
End Sub

 

[garypea123]

Seems to be perfect,

I think if I try to understand the script a little.

I do understand some of it but see below:

Option Explicit - I am not sure what this means

Sub test()
Dim c As Range, r As Range - R as range I fully understand but not sure about DIM c as range. (I believe DIM is used to indicate there are variables to the script?
Dim name As String - Again I need to understand more about DIM to understand this in full. I think it is saying that the DIM C is relating to the word (constant) "Ordered" and that are multiples of this.
Const to_find As String = "ordered" - This I understand

Set r = Sheet2.Range("F2:F" & Sheet2.Range("F" & Sheet2.Rows.Count).End(xlUp).Row) - This I understand
Debug.Print r.Address - This I have never seen before

For Each c In r
If StrConv(c, vbLowerCase) = to_find Then
name = c.Offset(0, -1) - I understand the concept of this but actually not sure fully what this is telling me
Else
c.Offset(0, -4) = name
End If
Next c

End Sub

I think I definitely need to go on some advanced VBA courses some where

 

[eirikdaude]

Option Explicit - I am not sure what this means

Option Explicit says that in the module containing this statement, all variables must be explicitly declared. It is smart to use this to e.g. avoid a misspelled variable to mess up your macro.

Dim c As Range, r As Range - R as range I fully understand but not sure about DIM c as range. (I believe DIM is used to indicate there are variables to the script?
Dim name As String - Again I need to understand more about DIM to understand this in full. I think it is saying that the DIM C is relating to the word (constant) "Ordered" and that are multiples of this.

Dim simply means that on the line starting with this I am declaring or dimensioning some variables. On the first line I dimension two range-type variables, on the second one string variable.

Debug.Print r.Address - This I have never seen before

This wasn't strictly necessary to get the function to work, it was just a line I included to help me with troubleshooting while writing the macro. Debug.Print prints whatever comes after it to the immediate window (you can bring it up by pressing Ctrl+G). In this case I wanted to double check that I had gotten the correct range assigned to the variable r earlier in the macro. See the code and screenshot below for what it might look like.

Sub test()
    Debug.Print "Test"
    Debug.Print Sheet1.Range("A1:A5").Address
    Debug.Print Sheet1.Range("A1:A5").Address(External:=True)
End Sub

For Each c In r
If StrConv(c, vbLowerCase) = to_find Then
name = c.Offset(0, -1) - I understand the concept of this but actually not sure fully what this is telling me
Else
c.Offset(0, -4) = name
End If
Next c

Here I loop through every cell in the range r.
If the value in the cell, c, is equal to the constant to_find when converted to lowercase I assign the value of the cell one step to the left of c to the variable name.
Otherwise I put the variable name into the cell 4 steps to the left of c.

If I'd wanted to use a cell to the right of c I'd have used a positive number as the second argument for offset. To move up or down a row from c, I'd use a negative or positive number for the first argument respectively. I hope this was of some help

 

[garypea123]

That is very useful, and yes it definitely gives me a much broader understanding of the script.

Thanks,
Gary

 

[garypea123]

Me again,

So this VBA works great in terms of the current sheet,

However, I seem to have an issue with the naming and getting to run on another sheet in my file.

My sheet names are Monday, Tuesday, Wednesday, Thursday, Friday.

I replaced the Sheet2 (3 occurances) with Monday on the VBA but I get a compile error (expected function or Variable. So it is clear that I am doing something wrong..

When I keep the sheet2 it works in Monday. But when I run the same macro for Tuesday Sheet, it just writes again in Monday sheet. Is there something I am doing wrong?

Thanks,
Gary

 

[eirikdaude]

Sheets in Excel have 2 names - its codename and "regular" name

If you use the codename you can refer to them as I did in my macro. If you use their regular name you need to use Worksheets("sheetname").

You can see both the codename and sheetname in the VBA editor if you have the project explorer and / or property window open. In the property window you can edit the codename too.