[Solved] Problem with Micro

Status
Not open for further replies.
Salam, Brother Rahman
Thank you very much for you time and effort. My son Noah have to submit the project by Oct 20th 2023. i know you are very busy person and I appreciate. the count of color cells worked fine. The problem is with the gap VBA, I am attaching the sheet for your consideration
Thank you again
Dear Habib

I hope this post will find you in good health. I have gone through the attached file that you shared in a conversation with me. Thanks for posting with such clarity. I have learned the specific requirements easily.

I am delighted to inform you that I have developed an Excel VBA sub-procedure that will fulfil your goal.

RESULT OF GAP or WHITE CELLS BETWEEN COLORED CELLS

Excel VBA Code
:
Code:
Sub DemoRStoreGapValuesWithColorBackgroundModified()

    Dim ws1 As Worksheet
    Dim col As Range
    Dim cell As Range
    Dim gapCount As Integer
    Dim coloredCellFound As Boolean
    Dim lastGapRow As Long
    Dim columnColor As Long
    Dim outputRow As Long
    Dim sourceColumn As Range
    Dim destColumn As Range
   
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
   
    outputRow = 1

    For Each col In ws1.Range("B1:Z1").Columns
        Set sourceColumn = ws1.Range(col.Cells(38, 1), col.Cells(57, 1))
        Set destColumn = ws1.Range(col.Cells(61, 1), col.Cells(80, 1))
       
        gapCount = 0
        coloredCellFound = False
        columnColor = RGB(255, 255, 255)
       
        For Each cell In sourceColumn
            If Not IsEmpty(cell.Value) And cell.Interior.Color <> RGB(255, 255, 255) Then
                If gapCount > 0 Then
                    destColumn.Cells(outputRow, 1).Value = gapCount
                    outputRow = outputRow + 1
                    gapCount = 0
                End If
                destColumn.Cells(outputRow, 1).Value = "R"
                destColumn.Cells(outputRow, 1).Interior.Color = cell.Interior.Color
                outputRow = outputRow + 1
                coloredCellFound = True
            Else
                gapCount = gapCount + 1
            End If
        Next cell
       
        If gapCount > 0 Then
            destColumn.Cells(outputRow, 1).Value = gapCount
        End If
       
        outputRow = 1
    Next col

End Sub

OUTPUT:
Output of running Excel VBA Code.png

Download the attached file for better understanding.

Regards
Lutfor Rahman Shimanto
 

Attachments

  • Habib (SOLVED).xlsm
    22.9 KB · Views: 2
Last edited:
Salam, Brother Rahman
Thank you very much. I will check and let you know
Take care love you all
 
Salam, Brother Rahman
When I add sheet3 its giving error. I don't have room on sheet1. I am attaching .xlm and pictures of error. Please fix it. I have only 3 days left
Thanks Take Care
 

Attachments

  • WHITE GAPS ERROR.jpg
    WHITE GAPS ERROR.jpg
    669.9 KB · Views: 1
  • WHITE GAPS ERROR 2.jpg
    WHITE GAPS ERROR 2.jpg
    593.4 KB · Views: 1
  • white gaps Habib ERROR 10-16-2023.xlsm
    26.9 KB · Views: 1
Salam, Brother Rahman
Thank you very much, the gap sheet calculation numbers are excellent, only need to add a sheet3 and I need to change the Range's. It will be nice if you show me, which entrees need to be replace.
Thanks Again. Take Care
 
Salam, Brother Rahman
Thank you very much, the gap sheet calculation numbers are excellent, only need to add a sheet3 and I need to change the Range's. It will be nice if you show me, which entrees need to be replace.
Thanks Again. Take Care
Wa alaykumu s-salam Habib

Thanks for staying with ExcelDemy Forum. It is to be mentioned that your appreciation means a lot to us.

I am working on your new requirement: placing the gap values on sheet3. When I am done, I will share the idea on this thread.

Regards
Lutfor Rahman Shimanto
 
Salam, Brother Rahman
When I add sheet3 its giving error. I don't have room on sheet1. I am attaching .xlm and pictures of error. Please fix it. I have only 3 days left
Thanks Take Care
Dear Habib

I hope this post finds you in good health. I am delighted to inform you that I have developed a sub-procedure to fulfil your new requirement.

As requested, the code will store the gap values and do other color-formattings within sheet3. I had to add other variables named ws3 and temp.

RESULT OF GAP or WHITE CELLS BETWEEN COLORED CELLS

Excel VBA Code
:
Code:
Sub Sheet3DestRStoreGapValuesWithColorBackgroundModified()

    Dim ws1, ws3 As Worksheet
    Dim col As Range
    Dim cell As Range
    Dim gapCount As Integer
    Dim coloredCellFound As Boolean
    Dim lastGapRow As Long
    Dim columnColor As Long
    Dim outputRow, temp As Long
    Dim sourceColumn As Range
    Dim destColumn As Range
    
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws3 = ThisWorkbook.Sheets("Sheet3")
    
    outputRow = 1
    temp = 1

    For Each col In ws1.Range("B1:Z1").Columns
    
        Set sourceColumn = ws1.Range(col.Cells(38, 1), col.Cells(57, 1))
        Set destColumn = ws3.Range(ws3.Cells(2, temp), ws3.Cells(22, temp))
        
        temp = temp + 1
        
        gapCount = 0
        coloredCellFound = False
        columnColor = RGB(255, 255, 255)
        
        For Each cell In sourceColumn
            If Not IsEmpty(cell.Value) And cell.Interior.Color <> RGB(255, 255, 255) Then
                If gapCount > 0 Then
                    destColumn.Cells(outputRow, 1).Value = gapCount
                    outputRow = outputRow + 1
                    gapCount = 0
                End If
                destColumn.Cells(outputRow, 1).Value = "R"
                destColumn.Cells(outputRow, 1).Interior.Color = cell.Interior.Color
                outputRow = outputRow + 1
                coloredCellFound = True
            Else
                gapCount = gapCount + 1
            End If
        Next cell
        
        If gapCount > 0 Then
            destColumn.Cells(outputRow, 1).Value = gapCount
        End If
        
        outputRow = 1
    Next col

End Sub

Where to Edit:
Modify the mentioned places to fulfil the goal.png

OUTPUT:
Output of running Excel VBA code to find gap values and store them in sheet3.gif

Hopefully, these ideas will help you reach your goal. I am also attaching the solution workbook. Good luck!

Regards
Lutfor Rahman Shimanto
 

Attachments

  • Habib (SOLVED).xlsm
    28.4 KB · Views: 1
Dear, Brother Rahman, Salam
Thank you for all the hard work, very much appreciated. When I applied the VBA script to a large data all the result values were wrong. since morning I was trying to resolve the issue, just few minuted back with the mercy of Allah SWT it got resolved. the problem was this. [For Each col In ws1.Range("B1:Z1").Columns.] this B1:Z1 made me crazy, I was about to give up. This VBA is a crazy and interesting language at the same time.

one request if possible to resolve the issues with Sine values and the Forecast before this Friday, it will be excellent and his project will be complete. please make the range referencing flexible, so different sheets can be used.

Thanks again for all the work and the problems because I do not know the VBA
Take. Care. Love you all
 
Thank you for all the hard work, very much appreciated. When I applied the VBA script to a large data all the result values were wrong. since morning I was trying to resolve the issue, just few minuted back with the mercy of Allah SWT it got resolved. the problem was this. [For Each col In ws1.Range("B1:Z1").Columns.] this B1:Z1 made me crazy, I was about to give up. This VBA is a crazy and interesting language at the same time.
Dear Habib

Thanks for thanking me. Your appreciation means a lot to the ExcelDemy Team. I am delighted to hear that you have resolved a new issue on your own. I agree with you that "VBA is a crazy and interesting language at the same time."

A Friendly Suggestion: Please post your new problem or a new requirement of your previous problem in a new thread with a proper heading.

For example, If you want a solution about finding Gap Values:

Heading:- [Excel VBA Code to Find Result of Gap or White Cells Between Colored Cells]
Explanation:- [Explain the requirement and additional information regarding problems.]
Attached File:- [If possible, share files or other documents.]

This will bring a win-win situation for both users and the ExcelDemy Team.

Thank you once again for staying with ExcelDemy Forum.

Regards
Lutfor Rahman Shimanto
 
Last edited:
one request if possible to resolve the issues with Sine values and the Forecast before this Friday, it will be excellent and his project will be complete. please make the range referencing flexible, so different sheets can be used.
Dear Habib

Your project deadline is before Friday. So, within this time duration, you need two Excel VBA Sub-Procedures to solve the Sine Values and the Forecast Values problems. As mentioned earlier, I went through these problem explanations (SINE & FORECAST) and did not understand them.

With proper explanation and guidance, the problem still seems complicated and time-consuming. I recommend you contact [email protected] with your project documents to get help from more professional Excel & VBA Developers. Good luck!

Regards
Lutfor Rahman Shimanto
 
Status
Not open for further replies.

Online statistics

Members online
0
Guests online
16
Total visitors
16

Forum statistics

Threads
337
Messages
1,470
Members
624
Latest member
duytoi
Top