Auto fill formula VBA with constant rule

garypea123

Board Regular
Joined
Mar 16, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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.

Test.PNG
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
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

Board Regular
Joined
Mar 16, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 16, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 26, 2013
Messages
60

ADVERTISEMENT

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?

VBA Code:
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

Board Regular
Joined
Mar 16, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 26, 2013
Messages
60

ADVERTISEMENT

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.

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

1607082385713.png


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 :)
 

Attachments

  • 1607082297690.png
    1607082297690.png
    8.6 KB · Views: 0

garypea123

Board Regular
Joined
Mar 16, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
That is very useful, and yes it definitely gives me a much broader understanding of the script.

Thanks,
Gary
 

garypea123

Board Regular
Joined
Mar 16, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 26, 2013
Messages
60
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.

1607345114396.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,789
Messages
5,626,893
Members
416,208
Latest member
tan21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top