Posts by Attilas

Re: Using drop down menus to auto populate data in another cell

Hi Montanaman

The Formula in B2 would be the following in your case

=VLOOKUP(A2;Sheet2!A:B;2;0)

The syntax of the VLOOKUP goes like this:

=VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])

Which can be translated into layman's terms as follows:

• lookup_value: This is what you want the formula to look for (ie. Bobs)

• table_array: This is the range in which you want the lookup to look for this value. Note that whatever you are looking for must be located in the first column of the range in order for the lookup to work-

• col_index: This number relates to the column in the specified range from which you want to retrieve the value.

• [range_lookup]: This is an optional parameter, you can specify it to be TRUE (or 1), or FALSE (or 0). True means that it will look for an approximative match. I have specified false so that you receive an exact match.

HTH

Regards

Attila

Re: Using drop down menus to auto populate data in another cell

Hi Montanaman

Welcome to Ozgrid.
Your question is rather broad. If you have a sheet with data which formulae can reference then you could use any of the following: VLOOKUP, HLOOKUP, MATCH (just to name a few).
If I understand correctly you have a drop down from Data validation?
If you want a more concrete example, it would help if you could supply some further information or upload a sample/dummy workbook.

Regards

Attila

Re: Formula Returning #N/A Rather than Blank

Well James,

You can check for T1 being blank with ISBLANK(T1). Depending on what you want to happen you could work another IF into there.

HTH

Regards

Attila

Re: Formula Returning #N/A Rather than Blank

Hi James

I just tried the following formula in a dummy WB, it works.

=IF(AND(VLOOKUP(K1;A:G;4;FALSE)="U";VLOOKUP(K1;A:G;6;FALSE)<L1);"LATYR";"")

There is nothing wrong with the logic in what you are using. I'd check if your VLOOKUPS are bringing valid values.

Regards

Attila

Re: Create new worksheets, naming them from range and inserting worksheet names in a

Hi Kotler

The reason why the Address is the first one is because you are simply looping through each cell in the MyRange which is adjacent.
I'd suggest that you change your code to the following:

Sub CopyAll()    On Error Resume Next
    Dim MyCell  As Range, MyRange As Range
     
    Set MyRange = Sheets("Source").Range("B3") 'company name
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
     
    With ThisWorkbook
        For Each MyCell In MyRange
            .Sheets("Master").Copy After:=Sheets(Sheets.Count) 'creates copy of Master template
            .Sheets(Sheets.Count).Name = MyCell.Value ' renames copy of master worksheet
            .Sheets(Sheets.Count).Range("A3").Value = MyCell.Value 'add company name in created copy
            .Sheets(Sheets.Count).Range("A4").Value = MyCell.Offset(0, 1).Value ' add address in created copy
        Next MyCell
    End With
     
    MsgBox ("Process completed succesfully")
     
End Sub

Note that I have removed Address, as it has become obsolete.

HTH

Regards

Attila

Re: How to assign a list to a named range?

Hi labkhand

It is because you are trying to assign a collection to your new named range and not a Range.
You could assign your selection to the named range directly instead of assembling all of the cell values into a collection first.
For future reference it would ease reading your code significantly if you were to use code brackets

HTH
Regards

Attila

Re: Protecting a sheet assigns macro to all cells near the button

My bad I skimmed through your code..... sorry mate.

Re: Create new worksheets, naming them from range and inserting worksheet names in a

Great!
Here is the addition:

.Sheets(Sheets.Count).Range("A4").Value = MyCell.Value

Full code

Sub CopyAll()    On Error Resume Next
    Dim MyCell  As Range, MyRange As Range
     
    Set MyRange = Sheets("Source").Range("B10") '
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
     
    With ThisWorkbook
        For Each MyCell In MyRange
            .Sheets("Master").Copy After:=Sheets(Sheets.Count) 'creates copy of Master template
            .Sheets(Sheets.Count).Name = MyCell.Value ' renames copy of master worksheet
            .Sheets(Sheets.Count).Range("A4").Value = MyCell.Value
        Next MyCell
    End With
     
    MsgBox ("Process completed succesfully")
     
End Sub

HTH

Regards

Attila

Re: Protecting a sheet assigns macro to all cells near the button

Hi NeverLift

What happens if you unprotect the sheet before selecting yòur shape?

ActiveSheet.Unprotect
Sheets("Volunteer Detail").Select
ActiveSheet.Shapes("DetailBack").Select
Selection.OnAction = "BackToSummary"
ActiveSheet.Protect

Regards

Attila

Re: Create new worksheets, naming them from range and inserting worksheet names in a

Hi Kotler

Try this:

Sub CopyAll()
    On Error Resume Next
    Dim MyCell  As Range, MyRange As Range
     
    Set MyRange = Sheets("Source").Range("B10") '
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
     
    With ThisWorkbook
        For Each MyCell In MyRange
            .Sheets("Master").Copy After:=Sheets(Sheets.Count) 'creates copy of Master template
            .Sheets(Sheets.Count).Name = MyCell.Value ' renames copy of master worksheet
        Next MyCell
    End With
    
    MsgBox ("Process completed succesfully")
    
End Sub

The part of your code copying the template was not inside the loop.

HTH

Regards

Attila

Re: Create a number value for a cell based on text in another cell

Hi Jenkin

Welcome to OzGrid.
Do you have a limited number of agents?
If so you could create a table in another sheet assigning a number to each agent and use a simple Vlookup Formula in column A.
You can create a list of the agents by copying column F into a new sheet and using the remove duplicates function in excel.

HTH

Regards

Attila

Re: help on combo box

Hi rikari

If you only want a dropdown to select values, I suggest using Data validation.
Creating such a large number of comboboxes is not advised.
How do you wish to filter the data?

Cheers

Attilas

Re: VBA code to graph data by find a certain name and using the data

Hi and welcome to OzGrid boarder236

I see that you would like to account for changes in location of the data in column G.
But where will this data be moved to, what types of changes do you wish to account for?

Could you possibly upload a sample workbook with dummy data illustrating your data structure, what change you wish to account for and what the end result should be?

Cheers

Attilas

Re: changing color of cell when an a number posted minus 90 days is todays date

Here is a working alternative:

Go into the conditional formatting window and select >Format only cells that contain

Format only cells with cell value between =TODAY() and =TODAY()+90

This should work for you.

HTH

Regards

Attila

Re: changing color of cell when an a number posted minus 90 days is todays date

Hi there

I'll take another look. I can't see the issue right now...

Re: Run macro on file close without being asked to save twice

Hey Jstrogue

Have you tried simply adding

Thisworkbook.Save

at the end?

Regards

Attila

Re: changing color of cell when an a number posted minus 90 days is todays date

Try again:

=AND(ABS(INDIRECT("R"&ROW()&"C"&COLUMN();FALSE))<=TODAY()+90;ABS(INDIRECT("R"&ROW()&"C"&COLUMN();FALSE))>TODAY())

it works for me.
Maybe you tried it just when I posted it there was some extra gibberish in there (sorry...)

Re: Match and place formula - Copy/paste

Hi JSpectrum

This is definitely possible, however, a formula in column B would work just as well (since that is where you want the ID to be)
No ID will be displayed whilst C is empty. Based on the pasted data the formula will the go an find a match on your reference sheet, or even assemble the ID on its own.
To help you write the formula, I would definitely need more specific information.

Regards

Attila

Re: changing color of cell when an a number posted minus 90 days is todays date

Just in case: these formulae imply that you use a date in column h and not a number

Re: changing color of cell when an a number posted minus 90 days is todays date

Select the custom formula option in the conditional formatting window and insert this:

=ABS(INDIRECT("R"&ROW()&"C"&COLUMN();FALSE)+$A$1:$B$705)<=TODAY()+90

HTH

Attila

Edit:
Note that this will keep your cells formatted that way even if they are in the past.
This formula fixes that

=AND(ABS(INDIRECT("R"&ROW()&"C"&COLUMN();FALSE))<=TODAY()+90;ABS(INDIRECT("R"&ROW()&"C"&COLUMN();FALSE))>TODAY())