Posts by 0zero0

I have the following code which deletes a row from a table ("EPAtable"). It deletes the row which contains the value selected in the "EPAlist" listbox which is on a userform.

dim lrow as long
dim str as string


On Error Resume Next
lrow = Application.WorksheetFunction.Match(EPAlist.value, Range("EPATable").Columns(1), 0)
On error GoTo 0
If lrow > 0 Then
     Sheet12.Range("EPATable").Rows([lrow]).EntireRow.Delete
End If
End If


With EPAList
     str = .RowSource
     .RowSource = vbNullString
     .Rowsource = str
End With


End Sub

This code works only sometimes. Sometimes when I run the macro, nothing happens, I get an error or excel crashes.

I am just learning VBA, so perhaps there is a better way to accomplish what it is I am trying to accomplish?

Re: Go down a column and check if values are present in a table

Works great! Thanks again

Re: Go down a column and check if values are present in a table

Wow, thank you for the quick response! Ideally, a dialogue would put up for name that is not present. i.e. "Bryan is not in the master list". User clicks OK, then "Darrel is not in the master list" and so on.

Reason is, I have an "Add employee" button that will be called up in the instance a crewmember is not included in MainTable, which will allow the user to complete a userform and add that person into the Main table.

But, I think I can poke through the code you provided and make that happen.

Thanks again!

Please see attached workbook.

I need a macro to check each name in range F2:F11 and determine if it exists in MainTable, column 1.

If it does, do nothing. If it does not, call a MsgBox.

I have tried a bunch of different methods, but I have the same problem with each one: the MsgBox is called after every instance of the value from range F2:F11 not existing in MainTable; i.e. it checks the cells one by one and calls the MsgBox each time the value is not present.

I would like the code to run through the entire table and then, at the end, call the MsgBox if the name from range F2:F11 was not found.

Said another way: Run through MainTable and check for the name "Bryan". If present, do nothing and move on to "Darrel". If not, call MsgBox. And do that for each name in range F2:F11forum.ozgrid.com/index.php?attachment/72780/

Re: Create a data validation list using only specific data from a table

That works great, thank you.

However, the "P" will not always be grouped together since the list will ultimately be sorted alphabetically by name.

Is it possible to modify the code to work in this instance?

Please see attached workbook.

I would like to create a dropdown list that only contains only the names in the first column that correlate to a "P" in the second column of the table.

The table will have rows added to it regularly and the dropdown list should update automatically.

I feel like this may be very easy, but I simply cannot figure out the data validation.forum.ozgrid.com/index.php?attachment/72483/

Re: Save a file as a pdf to folders dependent on the current date

I should have used "location" instead of "file name".

The location I need the file saved to is ...\2017\04 APR\09 APR *the value in cell D2*

the folder nomenclature is odd but it is already existing and cannot be changed. 04 APR is the month (April being the 4th month), 09 APR is the day (April 9; again, odd nomenclature) and the value in cell D2 is an auto populating 2 letter identifier for the day.

And that will be the date 2 days ahead of when this is generated.

Thank you for your help.

I have the following code to save a worksheet as a pdf when the button is pressed:

Sub SavePDF()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\9. Office12\msn plan share\2. Folders" & _
*******see below*******
    Worksheets("ATO Days").Range("D2").Value & ".pdf", _
    OpenAfterPublish:=True
End Sub

After 2. Folders\ I need the filename to be in this format but for the date 2 days after the current date:

2017\04 Apr\09 Apr **and whatever is in Worksheet "ATO Days", cell D2**

I tried putting the formatted dates into different cells on the sheet "ATO Days" (see attached) and referencing them for the filename, but to no avail.

I am very new to VBA.

Thank you for the help.

Re: Trying to implement a button to add data to an existing table

Works flawlessly. Thank you!

For my own educational purposes, can you explain the following:

lCol = Application.Match([Employee], [Employees], 0) + 2

Everything else makes sense to me except for this.

Thanks again.

See attached workbook.

I need the "Add" button to add the values entered in cells B7 and B8 to the value in the respective employee column as selected from the dropdown list in cell B6.

If selected "yes", cell B9 should add a 1 to the number in row 4 for the respective employee. If selected "no" it should not add anything to row 4.

I have tried to research and write the code needed, but I have literally gotten nowhere. I am somewhat excel proficient but not VBA proficient.

Thank you for your help.

Re: Referencing a cell in the current row

Thanks for the help. That code returned an error. I am unable to upload an image of my worksheet. It is a small .png file but it will not upload.

Everytime a row is added, Cell 5 in the row needs to reference cell 9 in that same row.

I have a button that adds a new row to a table and enters formulas into certain cells. The full code follows:

Private Sub CommandButton1_Click()Procedure1
Procedure2
End Sub
 
Sub Procedure1()
sheet_name_to_create = Sheet1.Range("O3").Value




For rep = 1 To (Worksheets.Count)
    If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
    MsgBox "This pilot already exists."
    Exit Sub
    End If
Next




    Sheets("New Pilot").Copy After:=Sheets("Summary")
    Sheets(ActiveSheet.Name).Name = sheet_name_to_create








End Sub
 
Sub Procedure2()




Dim the_sheets As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow




Set the_sheet = Sheets("Summary")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add




table_object_row.Range(1, 2).Value = Range("$O$3")
table_object_row.Range(1, 3).Formula = "=LOOKUP(Summary!$A$1," & Range("$O$3").Value & "!$B$9:$B$1312," & Range("$O$3").Value & "!$L$9:$L$1312)"
table_object_row.Range(1, 4).Formula = "=LOOKUP(Summary!$A$1," & Range("$O$3").Value & "!$B$9:$B$1312," & Range("$O$3").Value & "!$N$9:$N$1312)"
table_object_row.Range(1, 9).Formula = "=LOOKUP(Summary!$A$1," & Range("$O$3").Value & "!$B$9:$B$1312," & Range("$O$3").Value & "!$I$9:$I$1312)"
table_object_row.Range(1, 10).Formula = "=SUMPRODUCT(--(" & Range("$O$3").Value & "!$B$9:$B$1312>=EDATE(TODAY()-DAY(TODAY())+1,-6))," & Range("$O$3").Value & "!$J$9:$J$1312)"
table_object_row.Range(1, 11).Formula = "=SUMPRODUCT(--(" & Range("$O$3").Value & "!$B$9:$B$1312>=EDATE(TODAY()-DAY(TODAY())+1,-6))," & Range("$O$3").Value & "!$K$9:$K$1312)"

End Sub

I need the following formula to always reference the "J" column and whatever row was just added:

table_object_row.Range(1, 5).Formula "=IF(J**CURRENTROW**>3,""GO"",""NO GO"")"

Ive tried using" & DestRow " but it is not working. But I may have it entered incorrectly.

I was able to record the following macro and, although it works, I receive an error everytime the button is pressed:

ActiveCell.FormulaR1C1 = "=IF(RC[4]>=3,""GO"",""NO GO"")"Range("F6").Select

Thank you for the help.

Re: Adding code using VBA

Works perfectly! Thank you.

Re: Adding code using VBA

Almost! I just need the formula to reference the worksheet that has been named whatever is in cell L7.

So, if "test" is entered into cell L7 and the button is pressed, the following is entered into the respective cell:

=LOOKUP(Data!B2,testB9:B373,testN10:N374)

I need it to be:

=LOOKUP(Data!B2,test!B9:B373,test!N10:N374)

so that it will reference the newly created worksheet.

I have created a button that will insert a new row into a table and add a new worksheet. It will also rename the worksheet with whatever was inserted into Cell L7.

I also need the button to insert formulas into certain cells on the new row of the table.

The problem I am having is that the formula must contain a reference to the new worksheet that was created.

My code is below:

Private Sub CommandButton1_Click()
Procedure1
Procedure2
End Sub
 
Sub Procedure1()
sheet_name_to_create = Sheet1.Range("L7").Value




For rep = 1 To (Worksheets.Count)
    If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
    MsgBox "This pilot already exists"
    Exit Sub
    End If
Next




    Sheets("New Pilot").Copy After:=Sheets(Sheets.Count)
    Sheets(ActiveSheet.Name).Name = sheet_name_to_create








End Sub
 
Sub Procedure2()




Dim the_sheets As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow




Set the_sheet = Sheets("Summary")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add




table_object_row.Range(1, 2).Value = Range("L7")




[B]table_object_row.Range(1, 5).Formula = "=LOOKUP(Data!B2,**L7**B9:B373,**L7**N10:N374)"[/B]












End Sub

Where the "**L7**" is, is where I need whatever value was typed into cell L7 to be inserted.

I was asked to create this workbook by my employer, and have just started learning VBA to do it.

Thank you for the help.