If statement to change value of next empty cell in range on another sheet

[jchi2210]

Hello guys, I'm trying to wrap my head around this and it's driving me nuts.

I'm trying to setup an If statement so that if it's true, then it places text on a specific range on another sheet on the next empty cell in that range. I tried it with ranges and couldn't figure it out so just to get it working I tried working with just a specific row starting at a specific point and I seem to have broken that as well.

The Range to copy to on Templates is B12:G22. It's currently only changing the value of B12 in the "Template" sheet, which is great that I got that working. But I want to be able to hit the button again and it move to B13. If B13 has a value of any kind in it, then move on. After filling up B22, I was trying to get it to move to C12 and continue on. I know this is kind of over my head and I apologize.

If Sheets("Main").Range("Y4").Value = 2 Then
Sheets("Template").Range("B12").Value = 1
With Sheets("Template").Range("B" & Rows.Count).End(xlUp).Offset(12)
End With
End If

 

[Trebor76]

Hi jchi2210,

Let me know how this goes:

Option Explicit
Sub Macro1()

    Dim rng As Range
    Dim lngPasteRow As Long, lngPasteCol As Long
    
    Application.ScreenUpdating = False
    
    If Sheets("Main").Range("Y4").Value = 2 Then
        If WorksheetFunction.CountA(Sheets("Template").Cells) = 0 Then
            Sheets("Template").Range("B12").Value = 1
        Else
            Set rng = Sheets("Template").Cells
            On Error Resume Next 'Ok to suppress error message if there's no data on the tab
                lngPasteRow = rng.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                lngPasteCol = rng.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            On Error GoTo 0
            If lngPasteCol < 2 Then lngPasteCol = 2
            If lngPasteRow < 12 Then
                lngPasteRow = 12
            ElseIf lngPasteRow > 22 Then
                lngPasteRow = 12
                lngPasteCol = lngPasteCol + 1
            End If
            Sheets("Template").Range(CStr(rng.Parent.Cells(lngPasteRow, lngPasteCol).Address(False, False))).Value = 1
        End If
    End If
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[jchi2210]

Hi jchi2210,

Let me know how this goes:

Option Explicit
Sub Macro1()

    Dim rng As Range
    Dim lngPasteRow As Long, lngPasteCol As Long
   
    Application.ScreenUpdating = False
   
    If Sheets("Main").Range("Y4").Value = 2 Then
        If WorksheetFunction.CountA(Sheets("Template").Cells) = 0 Then
            Sheets("Template").Range("B12").Value = 1
        Else
            Set rng = Sheets("Template").Cells
            On Error Resume Next 'Ok to suppress error message if there's no data on the tab
                lngPasteRow = rng.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                lngPasteCol = rng.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            On Error GoTo 0
            If lngPasteCol < 2 Then lngPasteCol = 2
            If lngPasteRow < 12 Then
                lngPasteRow = 12
            ElseIf lngPasteRow > 22 Then
                lngPasteRow = 12
                lngPasteCol = lngPasteCol + 1
            End If
            Sheets("Template").Range(CStr(rng.Parent.Cells(lngPasteRow, lngPasteCol).Address(False, False))).Value = 1
        End If
    End If
   
    Application.ScreenUpdating = True
   
End Sub

Regards,

Robert

Hi Robert!

Thank you for taking the time to respond! I very much appreciate it.

I finally got a chance to test it, but it kept giving me an error that said "Compile error: Only comments may appear after End Sub, End Function, or End property"
I removed the "Option Explicit" at the beginning of the code and the error went away.

When I click the button that calls the Macro above, if it's true, it places a 1 on the new sheet, however instead of placing it at B12 it places it at C12. If I hit the button again, it places it at C13. It recognizes when C22 has a value in it as well and moves to D12 like it should, however after that it doesn't move down anymore. It begins placing values in E12, F12, G12, and so on after each click of the button.

Any suggestions? I feel like we are extremely close here

 

[Trebor76]

I finally got a chance to test it, but it kept giving me an error that said "Compile error: Only comments may appear after End Sub, End Function, or End property"
I removed the "Option Explicit" at the beginning of the code and the error went away.

There must have been extra text on the module you pasted my code into. Make sure the module you post the following code into is completely blank. It's highly recommended to have Option Explicit at the very top of every module.

When I click the button that calls the Macro above, if it's true, it places a 1 on the new sheet, however instead of placing it at B12 it places it at C12. If I hit the button again, it places it at C13. It recognizes when C22 has a value in it as well and moves to D12 like it should, however after that it doesn't move down anymore. It begins placing values in E12, F12, G12, and so on after each click of the button.

Any suggestions? I feel like we are extremely close here

See how this goes:

Option Explicit
Sub Macro1()

    Dim lngPasteRow As Long
    Dim lngMyCol As Long, lngLastCol As Long
    Dim blnDataPasted As Boolean
    Dim xlnCalcMethod As XlCalculation
   
    With Application
        .ScreenUpdating = False
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
    End With
   
    If Sheets("Main").Range("Y4").Value = 2 Then
        If WorksheetFunction.CountA(Sheets("Template").Cells) = 0 Then
            Sheets("Template").Range("B12").Value = 1
        Else
            lngLastCol = Sheets("Template").Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            For lngMyCol = 2 To lngLastCol
                lngPasteRow = Sheets("Template").Cells(Rows.Count, lngMyCol).End(xlUp).Row
                If lngPasteRow < 12 Then
                    Sheets("Template").Cells(12, lngMyCol).Value = 1
                ElseIf lngPasteRow >= 12 And lngPasteRow < 22 Then
                    Sheets("Template").Cells(lngPasteRow + 1, lngMyCol).Value = 1
                    blnDataPasted = True
                    Exit For
                End If
            Next lngMyCol
            If blnDataPasted = False Then
                Sheets("Template").Cells(12, lngLastCol + 1).Value = 1
            End If
        End If
    End If
   
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With
   
End Sub

 

[jchi2210]

There must have been extra text on the module you pasted my code into. Make sure the module you post the following code into is completely blank. It's highly recommended to have Option Explicit at the very top of every module.



See how this goes:

Option Explicit
Sub Macro1()

    Dim lngPasteRow As Long
    Dim lngMyCol As Long, lngLastCol As Long
    Dim blnDataPasted As Boolean
    Dim xlnCalcMethod As XlCalculation
  
    With Application
        .ScreenUpdating = False
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
    End With
  
    If Sheets("Main").Range("Y4").Value = 2 Then
        If WorksheetFunction.CountA(Sheets("Template").Cells) = 0 Then
            Sheets("Template").Range("B12").Value = 1
        Else
            lngLastCol = Sheets("Template").Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            For lngMyCol = 2 To lngLastCol
                lngPasteRow = Sheets("Template").Cells(Rows.Count, lngMyCol).End(xlUp).Row
                If lngPasteRow < 12 Then
                    Sheets("Template").Cells(12, lngMyCol).Value = 1
                ElseIf lngPasteRow >= 12 And lngPasteRow < 22 Then
                    Sheets("Template").Cells(lngPasteRow + 1, lngMyCol).Value = 1
                    blnDataPasted = True
                    Exit For
                End If
            Next lngMyCol
            If blnDataPasted = False Then
                Sheets("Template").Cells(12, lngLastCol + 1).Value = 1
            End If
        End If
    End If
  
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With
  
End Sub

I just got a chance to try the new code that you posted and made sure to put Option Explicit at the top. It accepts the Option Explicit now and it begins at Cell B12 like we want it to, but instead of placing a single "1" it's placing 3 of them, one in B12, one in C12, and one in C12. After the first run, it doesn't place 1's anywhere else other than in those cells. It may just be overwriting those cells each time though.

 

[Trebor76]

instead of placing a single "1" it's placing 3 of them, one in B12, one in C12, and one in C12. After the first run, it doesn't place 1's anywhere else other than in those cells. It may just be overwriting those cells each time though.

That's odd - that's not what's happening when I run it

Try stepping through the code to see what's happening. I cannot suggest anything else I'm afraid.

 

[jchi2210]

That's odd - that's not what's happening when I run it

Try stepping through the code to see what's happening. I cannot suggest anything else I'm afraid.

I went back through some of my code and changed some around some and I think I found the problem that was causing that issue. It's still starting at C12 instead of B12 and I've made sure to go through and clear content in that whole area to make sure it didn't think something was there. Other than the initial placement though, it's working great! Thank you so much for your help so far. If I may ask, if I wanted to change the placement of the range that it copies to, what values do I change? For instance, if I wanted to change this from B12:G22 to H10:K20, what parts would I change? This is just so I can reuse this in multiple places

 

[Trebor76]

if I wanted to change the placement of the range that it copies to

I think I've misunderstood what you're after as the code doesn't copy anything

I'm sure someone else on the forum will be able to help though

Good luck with it.

 

[jchi2210]

I think I've misunderstood what you're after as the code doesn't copy anything

I'm sure someone else on the forum will be able to help though

Good luck with it.

Sorry I didn't word it correctly, I meant where it's placing the 1 at. I believe it's setup so that if Y4 = 2 then B12:G22 will get a 1 everytime the button is clicked. I was just trying to figure out how to change the range B12:G22 to something different like to H10:K20, but I think I can figure it out. I don't want to take too much of your time. Thank you for helping me

 

[Trebor76]

I believe it's setup so that if Y4 = 2 then B12:G22 will get a 1 everytime the button is clicked. I was just trying to figure out how to change the range B12:G22 to something different like to H10:K20

So are you just after this:

Option Explicit
Sub Macro2()

    Application.ScreenUpdating = False

    If Sheets("Main").Range("Y4").Value = 2 Then
        Sheets("Template").Range("B12:G22").Value = 1
    End If
    
    Application.ScreenUpdating = True

End Sub

Just change the range in second line to suit (the range could even be in a cell that is referenced).