Posts by mrbuttons

Hello everyone,

I made a VBA in excel file where it triggers a message box if a value in the column doesn't exceed 8 characters. However, my formula doesn't work and the pop-up box still appears despite me keying in the value in the excel file.

.

Appreciate if you can have a look at the excel file. Thanks!

Hi all,

I have multiple excel files in a folder that I would like to combine them all into another excel file. Each time a new file is uploaded to the folder, the excel file should be updated without making changes to the old ones.

I tried using Power Query or Power BI but it keeps returning some errors hence why I'm looking for VBA/Makro help here.

Is there a VBA/Makro code I can refer to in combining the files?

Thanks!

Hello everyone,

I'm trying to achieve a simple conditional formatting where there are two conditions need to be achieved. The first condition is to find out which of the value in Column B is lesser or equal to 0.0500. I'm able to achieve this using the true false method with the formula below.

=Column B<=0.0500

Once the condition has been met, it will then need to find out which of these is the MAX (highest value) in Column C. For this, I'm facing some difficulties to achieve the second criteria conditional formatting.

I have attached the reference as per below. Thanks!

Thanks royUK !

Hi Carim

The test file is brilliant! With your help, I managed to get the result needed.

Thank you so much!

Hi Carim

Yes correct, sheet Template is my destination sheet whereas Hospital Info and Product Lists are the source sheets.

When I filter based upon the first column (Address Line 1), the respective cells from the first row should be copied from one worksheet to another.

That means, first cell of Column A in worksheet Hospital Info should be copied and pasted to Column A cell 9 in worksheet Template.

This is also the same for other cells as per the VBA code.

Sheets("Hospital Info").Range("A2").Copy Destination:=Sheets("template").Range("B9")

Sheets("Hospital Info").Range("B2").Copy Destination:=Sheets("template").Range("B10")

Sheets("Hospital Info").Range("C2").Copy Destination:=Sheets("template").Range("B11")

Sheets("Hospital Info").Range("D2").Copy Destination:=Sheets("template").Range("B12")

Sheets("Hospital Info").Range("E2").Copy Destination:=Sheets("template").Range("B14")

Sheets("Hospital Info").Range("F2").Copy Destination:=Sheets("template").Range("B15")

Sheets("Product List").Range("A2").Copy Destination:=Sheets("template").Range("B20")

Sheets("Product List").Range("B2").Copy Destination:=Sheets("template").Range("C20")

Am I confusing you? hahaha

Hello everyone,

I'm working on a simple automation to copy paste value from a worksheet to another worksheet. I'm not really an expert in VBA and I'm currently stuck in copying the first row of worksheet after the columns have been filtered.

As you can see below, I would like to copy the first row below to another worksheet and in a specific cells.



*To a different worksheet and in specific cells*


the VBA code I'm using is a standard code as per below.

Sub Copy_First_Row()


'First step: Once filter, copy the cell respectively to another cell in another worksheet


Sheets("Hospital Info").Range("A2").Copy Destination:=Sheets("template").Range("B9")
Sheets("Hospital Info").Range("B2").Copy Destination:=Sheets("template").Range("B10")
Sheets("Hospital Info").Range("C2").Copy Destination:=Sheets("template").Range("B11")
Sheets("Hospital Info").Range("D2").Copy Destination:=Sheets("template").Range("B12")
Sheets("Hospital Info").Range("E2").Copy Destination:=Sheets("template").Range("B14")
Sheets("Hospital Info").Range("F2").Copy Destination:=Sheets("template").Range("B15")


Sheets("Product List").Range("A2").Copy Destination:=Sheets("template").Range("B20")
Sheets("Product List").Range("B2").Copy Destination:=Sheets("template").Range("C20")


End Sub

How can I add to take the filtered first row only and the copy paste it to the other worksheet if we are using my code above?

Thanks!

Thank you so much royUK ! It works!

Hello everyone,

I have a user form that is being keyed in by multiple users. I have enabled password protected with certain range to be allowed.

However, upon doing so it triggers an error saying that the cell or chart is in a protected sheet.

How can I enable the form to be used for all worksheet with the password encrypted in it?

Is possible to encrypt the password without VBA and allowing the form to work?

Thanks!

Thank you so much Rory!

Hello everyone,

I have an excel file that have multiple formulas. When new rows are added, the formula works fine and will show.



However, when I locked the highlighted columns in yellow with password, the formula doesn't work anymore as per below.

Are there any additional formatting or setting that I missed out in sheet/cells protection?

I have attached the excel file there. The password is 123.

Thanks!

Hi alansidman

Thank you so much! Your explanation is correct and I understand it well. However, I keep getting these errors to some of those date/time.



Does this mean I can't change the date/time for those in errors?

Thank you so much!

My apologies. Here you go! Thank you so much!

Hi there,

I have a data that shows date/time data. Currently, I couldn't transform the date/time format into a specific one due to unknown reasons.



As you can see above, some of the date/time are in different formats. Whenever I tried to change the date/time format through TEXT Formula or through cells formatting, it doesn't seem to work well.



The date/time format should be in "MM/DD/YYYY" which only works on certain cells.

Are there any ways I can further change the date/time format here? Thank you very much!

Hi rory ,

thank you! it works!

Hi there,

I have a file that I would need to group the time into a specific shift. For example,

6.45am to 3.15pm falls under MORNING

3.15pm to 11.45pm falls under EVENING

11.45pm to 6.45am falls under NIGHT

I managed to separate the time from the existing datetime column. However, I'm stuck with the grouping using formulas.

Is it possible to group the time into specific shifts using formulas? Thank you!

Hi there,

Thanks! The subtotal function works!

Hello everyone,

I have a data collection file with some calculation created. I would like to get my calculations (Column O) to work when I filter the table. How can I get the calculations to work when I filter the table?

Attached here is the file for your references. Appreciate the help and support from you guys here! Thank you so much!

Hi Roy,

Thanks! This works perfectly!

However, how can I automate it to the entire row of the column? Assuming if there are few rows added, will the formula work for subsequent rows?

Hi there,

Apologize for the error and confusion. Here's the updated file for you to view.

Thanks!