[Solved] Problem with Micro
- Thread starter Haas
- Start date
- Status
Wa alaykumu s-salam Haas
You are always welcome, and your appreciation means a lot to us.
Regards
Lutfor Rahman Shimanto
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 SubOUTPUT:
Download the attached file for better understanding.
Regards
Lutfor Rahman Shimanto
Last edited:
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
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 SubWhere to Edit:
OUTPUT:
Hopefully, these ideas will help you reach your goal. I am also attaching the solution workbook. Good luck!
Regards
Lutfor Rahman Shimanto
Wa alaykumu s-salam Habib
You are most welcome! Let me know how your project is going.
Regards
Lutfor Rahman Shimanto
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.
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
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:
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