Posts by Fluff13

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then
 With Sheets("Form")
   Select Case Target.Value
   
   Case Is = "Not Applicable": .Rows("12:18").EntireRow.Hidden = True
   .Range("A1:A11").EntireRow.Hidden = False
   
   Case Is = "1": .Rows("14:18").EntireRow.Hidden = True
   .Range("A1:A13").EntireRow.Hidden = False
   Case Is = "2": .Rows("15:18").EntireRow.Hidden = True
   .Range("A1:A14").EntireRow.Hidden = False
   
   Case Is = "3": .Rows("16:18").EntireRow.Hidden = True
   .Range("A1:A15").EntireRow.Hidden = False
   
   Case Is = "4": .Rows("17:18").EntireRow.Hidden = True
   .Range("A1:A16").EntireRow.Hidden = False
   Case Is = "5": .Rows("18:18").EntireRow.Hidden = True
   .Range("A1:A17").EntireRow.Hidden = False
   Case Is = "6"
   .Range("A1:A18").EntireRow.Hidden = False
   End Select
 End With
End If
End Sub

Function Ginnerman(Cl As Range) As Long
   Dim Sp As Variant
   
   On Error Resume Next
   If InStr(1, Cl, "m", 1) = 0 Then Sp = Split("m" & Cl, "m") Else Sp = Split(Cl, "m")
   If IsNumeric(Sp(0)) Then Ginnerman = Sp(0) * 1760
   If InStr(1, Sp(1), "f", 1) = 0 Then Sp = Split("f" & Sp(1), "f") Else Sp = Split(Sp(1), "f")
   If IsNumeric(Sp(0)) Then Ginnerman = Ginnerman + Sp(0) * 220
   Sp = Split(Sp(1), "y")
   If IsNumeric(Sp(0)) Then Ginnerman = Ginnerman + Sp(0)
End Function

Sub shoaibRajput()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   Ary = Sheets("Data").Range("a1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 5)
   For c = 5 To UBound(Ary, 2)
      For r = 2 To UBound(Ary)
         If Ary(r, c) = "X" Then
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            Nary(nr, 2) = Ary(r, 2)
            Nary(nr, 3) = Ary(r, 3)
            Nary(nr, 4) = Ary(r, 4)
            Nary(nr, 5) = Ary(1, c)
         End If
      Next r
   Next c
   Sheets("2nd Example").Range("G2").Resize(nr, 5).Value = Nary
End Sub

Sub Redjohno()
   Dim Ary1 As Variant, Ary2 As Variant, Nary As Variant
   Dim i As Long, j As Long, k As Long
   
   With Sheets("Sheet1")
      Ary1 = .Range("C7", .Range("C" & Rows.Count).End(xlUp)).Value2
      Ary2 = .Range("D1", .Cells(1, Columns.Count).End(xlToLeft)).Value2
   End With
   ReDim Nary(1 To UBound(Ary1) * UBound(Ary2, 2), 1 To 4)
   For i = 1 To UBound(Ary1)
      For j = 1 To UBound(Ary2, 2)
         k = k + 1
         Nary(k, 1) = Ary1(i, 1)
         Nary(k, 4) = Ary2(1, j)
      Next j
   Next i
   Sheets("sheet2").Range("B1").Resize(UBound(Nary), 4).Value = Nary
End Sub

Sub Redjohno()
   Dim Ary1 As Variant, Ary2 As Variant, Nary As Variant
   Dim i As Long, j As Long, k As Long
   
   With Sheets("Sheet1")
      Ary1 = .Range("C7", .Range("C" & Rows.Count).End(xlUp)).Value2
      Ary2 = .Range("D1", .Cells(1, Columns.Count).End(xlToLeft)).Value2
   End With
   ReDim Nary(1 To UBound(Ary1) * UBound(Ary2, 2), 1 To 2)
   For i = 1 To UBound(Ary1)
      For j = 1 To UBound(Ary2, 2)
         k = k + 1
         Nary(k, 1) = Ary1(i, 1)
         Nary(k, 2) = Ary2(1, j)
      Next j
   Next i
   Sheets("sheet2").Range("A1").Resize(UBound(Nary), 2).Value = Nary
End Sub