Posts by 01652845663

Hi Hal,

I still not understand your request, can you attach your excel file?

Hi Magdoulin,

Because your list range of filter is not right. You can check the list range by clicking "Advanced" button(the right of "filter" button).
Make sure choose the list range that you want to filter before clicking "filter" button for later other wise the excel will choose automatically and sometime it choose wrongly.

Hope I can help.
VietNT

You are welcome. Glad to hear that the code worked at your site.
If you have other concerns, you can post it on this forum or send me a message, I'll help you when I'm free.

Hi Natrjc,

Don't worry bro, practice makes perfect. I will help you at VB if I can

Let try as my instruction below:
1. Open the file and unprotect sheet(I think you know how)
2. Choose all cells in the sheet(you can click the top left corner)-->right click and choose "format cell"-->at "protection"tab, make sure"clocked" is clicked-->click OK(what I need you is to clock all cells)
3. Copy below code into you VBA

Private Sub Workbook_Open()
Dim i, j As Long
With Sheets("Drivezy")
.Unprotect "test"
For i = 5 To 36
If .Cells(5, i).Value >= Date Then
For j = 7 To 25
.Cells(j, i).Locked = False
Next j
Else
For j = 7 To 25
.Cells(j, i).Locked = True
Next j
End If
Next i
.Protect "test"
End With
End Sub

4. Save and re-run your excel file

Let's try and see the result, let me know if you have concern

---Viet

Try to use the code below, it will help you

Private Sub Workbook_Open()
Dim i As Long
With Sheets("Drivezy")
.Unprotect "test"
For i = 5 To .UsedRange.Columns.Count
If .Cells(5, i).Value >= Date Then
.Columns(i).Locked = False
Else
.Columns(i).Locked = True
End If
Next i
.Protect "test"
End With
End Sub

You can use VBA in this case.
Place Animals sheet into sheet1 and other sheets into sheet2, sheet3,...
use function sheets for comparision through each sheet
example sheets(1).cells(1,1).value="chicken"
sheets(2).cells(1,2).value=500
use name property of sheets function to get the name of sheet2, sheet3,... note that you need to name the sheet2 like "Joes Farm" when you use function sheets(2).name will be "Joes Farm".
use for loop for get sheet from sheet2 to sheetn if you have many sheet, this make your file run automantically when you add new sheet of farm of someone.
I think if you know VBA you will make this app easy. HOpe you can do it.

More information, if you have 300 rows, then you can change the max to 300.

Hi Jack,

You can refer my code below, it can help you on your concern.
The first thing you should do is copy your table into sheet1, then add a button and add my code into the button-->click button and see your result is in sheet2. Hope can help.

Private Sub CommandButton1_Click()
Dim RowIndex, ColunmIndex, max As Integer
max = 5
For ColunmIndex = 2 To max + 1
For RowIndex = max To 1 Step -1
If RowIndex > ColunmIndex - 1 Then
Sheets("Sheet2").Range(Split(Cells(1, ColunmIndex).Address, "$")(1) & Trim(Str(RowIndex + 1))).Value = _
Sheets("Sheet1").Range(Split(Cells(1, 2).Address, "$")(1) & Trim(Str(ColunmIndex - 1))).Value - _
Sheets("Sheet1").Range(Split(Cells(1, 2).Address, "$")(1) & Trim(Str(RowIndex))).Value
Else
Sheets("Sheet2").Range(Split(Cells(1, ColunmIndex).Address, "$")(1) & Trim(Str(RowIndex + 1))).Value = "x"
End If
Next RowIndex

Next ColunmIndex
End Sub