Snasui Excel Forum

Code: Select all

'Other code
Dim lastCell As Range, s As String
s = Worksheets("Test").Range("d2").Value
With Sheets("Form")
   If s = "ชาย" Then
       Set lastCell = .Range("A3:F100000").Offset(1, 0).Find("*", _
           searchorder:=xlByColumns, searchdirection:=xlPrevious)
       If lastCell Is Nothing Then
           Set lastCell = .Range("a4")
           lastcol = 2
       End If
   Else
       Set lastCell = .Range("H3:Q100000").Offset(1, 0).Find("*", _
           searchorder:=xlByColumns, searchdirection:=xlPrevious)
       If lastCell Is Nothing Then
           Set lastCell = .Range("h4")
           lastcol = 9
       End If
   End If
   
'        lastrow = .Range("a" & .Rows.Count).End(xlUp).Row + 1
   
   If lastCell.Row = 4 Then
       lastrow = 4
       lastcol = lastCell.Column
       If lastCell.Value <> "" Then
           lastrow = lastrow + 1
           lastcol = lastCell.Column - 1
       End If
   ElseIf lastCell.Row >= 10 Then
       lastrow = 4
       lastcol = lastCell.Column + 1
   Else
       lastrow = lastCell.Row + 1
       lastcol = lastCell.Column - 1
   End If
'Other code

Code: Select all

Sub Macro1()
Dim a, n&, r&, c&
      a = [B2:C2].Value2
     With Sheets("Form")
        n = Application.CountA(.[A2:Z10]) / 2
        r = n Mod 9 + 2
        c = Int(n / 9) * 2 + 1
        .Cells(r, c).Resize(, 2) = a
    End With
        [B2:C2].ClearContents
        [B2].Select
End Sub

Code: Select all

 Set lastCell = .UsedRange.Offset(1, 0).Find("*", _
            searchorder:=xlByColumns, searchdirection:=xlPrevious)

Code: Select all

Sub Macro1()
'
' Macro1 Macro
'

'
     Dim lastCell As Range
     With Sheets("Form")
       Set lastCell = .UsedRange.Offset(1, 0).Find("*", _
            searchorder:=xlByColumns, searchdirection:=xlPrevious)
        
'        lastrow = .Range("a" & .Rows.Count).End(xlUp).Row + 1
        If lastCell Is Nothing Then
            lastrow = 2
            lastcol = 1
        ElseIf lastCell.Row >= 10 Then
            lastrow = 2
            lastcol = lastCell.Column + 1
        Else
            lastrow = lastCell.Row + 1
            lastcol = lastCell.Column - 1
        End If
        .Cells(lastrow, lastcol).Resize(1, 2).Value = Sheets("TEST").Range("b2:c2").Value
'        .Range("a" & lastrow) = lastrow - 1
        
'        .Range("a" & lastrow, .Range("b" & lastrow)).Value = _
            Sheets("TEST").Range("b2:c2").Value
    End With


    Sheets("TEST").Select
    Range("B2:C2").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("B2").Select
End Sub

Code: Select all

 Sub Macro1()
'
' Macro1 Macro
'

'

     With Sheets("Form")
       
        lastrow = .Range("a" & .Rows.Count).End(xlUp).Row + 1
        
        .Range("a" & lastrow) = lastrow - 1
        
        .Range("a" & lastrow, .Range("b" & lastrow)).Value = _
            Sheets("TEST").Range("b2:c2").Value
        
    End With


    Sheets("TEST").Select
    Range("B2:C2").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("B2").Select
End Sub 

Code: Select all

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row >= 10 Then
    Cells(Target.Row - 8, Target.Column + 2).Select
    End If
End Sub