Subramanian
New Member
- Joined
- May 14, 2013
- Messages
- 1
Hi,
I'm new to Excel VBA. I have almost done with 50% of the requirement, after that i'm not able to move further. Kindly someone help me out on this.
Requirement:
1. I have a excel sheet to get the dependencies.
2. The rows and columns are dynamic, so i have a problem in selecting the range.
3. The first thing is i need to check the value entered in another worksheet is matching with any of the row in the main sheet.
4. If it matches then i need to check if any of values in that row is more than "0". (I'm done until this part)
5. If any of the row value is more than "0" then i need to check if any of the values in that column has the value "0".
6. If any of the values in that column has the value "0" then i need to check if any of values in that row is more than "0". (this loop goes on)
7. Then if any of the values in that column is not "0" then i need to print the value in the row 3 of that column.
NOTE : I'm done until Point no "4" and i also did point no "7". Please find below the code which i wrote. The part in Red color has the prob. Kindly someone help me out and get it done.
Sub Data_Validation()
Dim iLastRow As Integer, currRow As Integer, currCol As Integer, depMain As Integer, depRow As Integer
Dim Row As Range
Dim Row1 As Range
Dim Col As Range
iLastRow = 200
currRow = 4
currCol = 0
depMain = 4
depRow = 3
For Each Row In Worksheets("Dependency Matrix").Range("B5:B" & iLastRow)
currRow = currRow + 1
If Row.Value = Worksheets("Data").Cells(4, 1) Then
For Each Col In Worksheets("Dependency Matrix").Range("D" & currRow & ":HV" & currRow)
currCol = currCol + 1
If Col.Value > 0 Then
For Each Row1 In Worksheets("BW Service Dependency Matrix").Range("5" & currCol & ":174" & currCol)
If Row1.Value = 0 Then
Range("D5").Value = Row1.Value
End If
Next Row1
Else
Range("C" & depMain).Value = Worksheets("BW Service Dependency Matrix").Cells(depRow, Col.Column)
Range("B" & depMain).Value = Col.Value
depMain = depMain + 1
End If
Next Col
Exit For
End If
Next Row
End Sub
I'm new to Excel VBA. I have almost done with 50% of the requirement, after that i'm not able to move further. Kindly someone help me out on this.
Requirement:
1. I have a excel sheet to get the dependencies.
2. The rows and columns are dynamic, so i have a problem in selecting the range.
3. The first thing is i need to check the value entered in another worksheet is matching with any of the row in the main sheet.
4. If it matches then i need to check if any of values in that row is more than "0". (I'm done until this part)
5. If any of the row value is more than "0" then i need to check if any of the values in that column has the value "0".
6. If any of the values in that column has the value "0" then i need to check if any of values in that row is more than "0". (this loop goes on)
7. Then if any of the values in that column is not "0" then i need to print the value in the row 3 of that column.
NOTE : I'm done until Point no "4" and i also did point no "7". Please find below the code which i wrote. The part in Red color has the prob. Kindly someone help me out and get it done.
Sub Data_Validation()
Dim iLastRow As Integer, currRow As Integer, currCol As Integer, depMain As Integer, depRow As Integer
Dim Row As Range
Dim Row1 As Range
Dim Col As Range
iLastRow = 200
currRow = 4
currCol = 0
depMain = 4
depRow = 3
For Each Row In Worksheets("Dependency Matrix").Range("B5:B" & iLastRow)
currRow = currRow + 1
If Row.Value = Worksheets("Data").Cells(4, 1) Then
For Each Col In Worksheets("Dependency Matrix").Range("D" & currRow & ":HV" & currRow)
currCol = currCol + 1
If Col.Value > 0 Then
For Each Row1 In Worksheets("BW Service Dependency Matrix").Range("5" & currCol & ":174" & currCol)
If Row1.Value = 0 Then
Range("D5").Value = Row1.Value
End If
Next Row1
Else
Range("C" & depMain).Value = Worksheets("BW Service Dependency Matrix").Cells(depRow, Col.Column)
Range("B" & depMain).Value = Col.Value
depMain = depMain + 1
End If
Next Col
Exit For
End If
Next Row
End Sub