Posts by woon liang

woon liang · Dec 30th 2022

Below is my source code and i feel this could be simplified alot more

Code

Sub searchDate()
Dim strDate As String
Dim rCells As Range
Dim dCells As Date

strDate = Range("I1").Value
dCells = Range("I1").Value

strDate = Format(strDate, "Short Date")

Set rCell = Cells.Find(What:=CDate(strDate), After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

'rCell.Offset(0, 1).Activate

If dCells >= rCell.Offset(0, 1) And dCells <= rCell.Offset(0, 2) Or dCells >= rCell.Offset(0, 3) And dCells <= rCell.Offset(0, 4) Then
    MsgBox ("Restricted Date")
Else
    MsgBox ("OK Date")
End If
End Sub

Display More

woon liang · Dec 29th 2022

i have a list of dates with time for the whole year of 2023.

I will need to program an excel vba let say if i have a date and time :

15-Feb 01:25 HRS -> msgbox ("date clash")

16-Feb 22:45 HRS -> msgbox ("date clash")

Any date and time that fall between the below date from to and date from (2) date to(2) will prompt that time & date is clashed.

For e.g.

DATE FROM	DATE TO	DATE FROM (2)	DATE TO (2)
14-Feb 21:00	14-Feb 23:59
15-Feb 00:00	15-Feb 03:00	15-Feb 21:00	16-Feb 00:00
16-Feb 00:00	16-Feb 04:00	16-Feb 21:00	17-Feb 00:00
17-Feb 00:00	17-Feb 06:00	17-Feb 22:00	18-Feb 00:00
18-Feb 00:00	18-Feb 07:00
19-Feb 01:00	19-Feb 09:00	19-Feb 15:00	19-Feb 16:00
20-Feb 04:00	20-Feb 10:00	20-Feb 16:00	20-Feb 17:00
21-Feb 06:00	21-Feb 11:00
22-Feb 08:00	22-Feb 11:00

woon liang · Oct 18th 2022

Hi Experts! I'm experiencing intermittent "The Following Error occured: Method 'Range' of object_Global" Failed from the below codes can some experts advise what's going on?

Code

 With Range("V:AA")
            .Font.Color = vbBlack
            .Font.TintAndShade = 0
            .Font.Bold = False
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            '.Interior.ColorIndex = 0
End With

woon liang · Oct 9th 2022

Problem resolved.

My other codes are flooded with conditional formatting. Thus, making the code very very slow to run. Hence, removed all conditional formatting and now it's back to normal.

woon liang · Oct 9th 2022

i somehow found the problem.

it's with deleting and inserting rows. I am running on excel 2013 and excel 2019. Both running very slow on deleting and adding rows. any work-around for this issue?

woon liang · Oct 8th 2022

Btw i am doing running this code for awhile and after that the code becomes very laggy.

initially it just took me less than 1sec to run. after awhile it's taking me 4-5secs just to run the above same code.

Don't understand at all.

woon liang · Oct 8th 2022

Can some expert enlighten me, how can the below code take 4secs to run? Btw i am using a timer to check the below code and it took me 4secs to run just this little stuff. Btw the lastrow will never exceed 1000 rows

Code

Sub rows_insert()

With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
End With

'Place your macro code here''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim x As Integer
Dim i As Integer
Dim lastROW As Long
    
lastROW = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row


    For i = 29 To lastROW
        If Cells(i, 3).Value = "HELLO" Then
            Rows(i).EntireRow.Delete
            i = i - 1
        End If
    Next i
    
    
    For x = 2 To 26
            If Cells(x, 9) <> "" Then
                Range("40:40").Insert
                Range("A40:AD40").Interior.Color = RGB(180, 150, 250)
                Cells(40, 3) = "HELLO"
                Cells(40, 7).Value = Cells(x, 7).Value      'Data A
                Cells(40, 8).Value = Cells(x, 10).Value     'Data B
                Cells(40, 19).Value = Cells(x, 8).Value     'Data C
                Cells(40, 21).Value = Cells(x, 9).Value     'Data D
                Cells(40, 1).Value = "Ok"
                Cells(40, 26).Value = Cells(x, 11).Value     'Data E
            End If
    Next x
    
    Range("A28:AE900").Sort key1:=Range("G28"), order1:=xlAscending, Header:=xlYes

    ThisWorkbook.Save
    
    
'end of macro''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

  
With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .DisplayStatusBar = True
        .Calculation = xlCalculationAutomatic
End With

End Sub

Display More

woon liang · Sep 19th 2022

Hi experts!

i am trying to use match to get upper and lower values of the horizontal and vertical data but i am only able to get the lower data and not the upper data.

The purpose of getting upper and lower data is to prorate them and get the pro-rated value from 4 value. (upper & lower value of x-axis AND upper & lower value of y-axis)

having the 4 values and prorate accordingly and now it seems like i am able to only get one value instead of all the 4 value to prorate.

Attached is the excel sheet for reference.

index_match.xlsx

woon liang · Jun 5th 2022

OMG thank you so much!

woon liang · Jun 4th 2022

Options explicit Error, i am getting this invalid procedure error with the below code. can some experts here tell me why? i declared all my variables already. I'm using excel 2013.

Code

Options Explicit

Sub test123()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim x As Integer

            For x = 1 To 10


            Next x
End Sub

Display More

Posts by woon liang

Search for date range - whole year

Search for date range - whole year

method of Range OBJECT GLOBAL failed for with range.

Adding Rows using VBA, Code taking too long to run.

Adding Rows using VBA, Code taking too long to run.

Adding Rows using VBA, Code taking too long to run.

Adding Rows using VBA, Code taking too long to run.

Index Match with proration of upper and lower values.

options explicit error

options explicit error