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

jchi2210

New Member
Joined
Apr 12, 2021
Messages
8
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,744
Hi jchi2210,

Let me know how this goes:

VBA Code:
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

New Member
Joined
Apr 12, 2021
Messages
8
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hi jchi2210,

Let me know how this goes:

VBA Code:
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,744
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:

VBA Code:
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
 
Solution

jchi2210

New Member
Joined
Apr 12, 2021
Messages
8
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,744
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 :confused:

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

jchi2210

New Member
Joined
Apr 12, 2021
Messages
8
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

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

Well-known Member
Joined
Jul 23, 2007
Messages
4,744
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

New Member
Joined
Apr 12, 2021
Messages
8
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,744
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:

VBA Code:
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,132,704
Messages
5,654,829
Members
418,155
Latest member
demasisi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top