VBA: if cell isn't empty, copy contents. if empty, move to next row.

[jpringle1]

OK so i just need a simple piece of VBA code which does the following things:

if cell B2 isn't empty, assign its value to a variable.
if it is empty, run this same code against the next row.
Continue until the bottom of the table is reached.

this piece of code of code is going to be adapted for a bigger project, so i will likely come back to ask for help on modifications a few times after this has been answered. The reason i only want a part of it answered at a time is because i want to understand the code so i can adapt it myself for future projects, and i'm not sure exactly which direction i want to take the code in yet.

Thank you

 

[Michael M]

How many rows do you need to test ?
How many variables might there be ?
Does the 2nd variable replace the 1st variable ?

 

[jpringle1]

How many rows do you need to test ?
How many variables might there be ?
Does the 2nd variable replace the 1st variable ?

For the sake of this example, let's say 20? But if possible I would like the amount to be dynamic, so if the size of the table changes, so does the amount of rows that get checked.

Also, there would only be one variable, so it would get replaced each time (I'm planning on inserting instructions for what to do with that variable in between row checks, but let's forget about that for now as I want to take this one step at a time)

 

[fhqwgads]

for the first bit you can try

If Not IsEmpty(Range("B2").Value) Then
Range("B2").Value = [your variable here]
Else
[code for when B2 is empty]
End If

depending on what your table looks like, like if it's on a sheet on its own or in a sheet with other data, looping through your table might be different

 

[Michael M]

Maybe this, x being your variable

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
  For r = 2 To lr
     If Range("b" & r).Value <> "" Then x = Range("b" & r).Value
  Next r
End Sub

 

[MARK858]

Just a different method of looping (wouldn't have posted it except I had it written when Michael posted so might as well post it before I delete it).

Sub GetVAR()
    Dim myVar, myCell As Range
    For Each myCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If myCell.Value <> "" Then myVar = myCell.Value
    Next
End Sub

Although as you are doing something with the variable at each stage then you probably need

Sub GetVAR()
    Dim myVar, myCell As Range
    For Each myCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If myCell.Value <> "" Then
            myVar = myCell.Value
            ' Do something with the variable here
        End If
    Next
End Sub

 

[TotallyConfused]

Hello jpringle
Let me WELCOME you to the site. Here is a bit of code I've put together that I think will cover what you have told us so far.

[COLOR=black][FONT=Calibri]Option Explicit[/FONT][/COLOR]

  [COLOR=black][FONT=Calibri]Sub jPringle1()[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' https://www.mrexcel.com/forum/excel-questions/1086969-vba-if-cell-isnt-empty-copy-contents-if-empty-move-next-row.html[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' February 10, 2019 @ 5:20 pm[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]' This is better than using a sheet name in case the user changes the tab name at a later date.[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim bCell As Variant         ' Variable to hold value of cell in 'B' column[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim NumRows As Long    ' Number of data rows NOT counting heading[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]   Dim RwCntr As Long         ' Loop counter to count number of rows of data processed[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Application.ScreenUpdating = False[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]RwCntr = 2[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]NumRows = ws1.Cells(Rows.Count, "B").End(xlUp).Row - 1  'Determine number of rows of actual data[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]With ws1[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]    For RwCntr = 2 To NumRows + 1[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]'  If 'B' column cell is empty, get next cell, otherwise assign value to variable of bCell[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]        If .Cells(RwCntr, 2) <> "" Then[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]            bCell = .Cells(RwCntr, 2).Value[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]        End If[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]     Next RwCntr[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]End With[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]Application.ScreenUpdating = True[/FONT][/COLOR]
  [COLOR=black][FONT=Calibri]End Sub[/FONT][/COLOR]

I hope this helps.

TotallyConfused

 

[MARK858]

Hi TotallyConfused, if I am reading what you have posted correctly and you are intending using the sheets codename rather than it's tab name shouldn't

  ' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.
  ' This is better than using a sheet name in case the user changes the tab name at a later date.
     Dim ws1 As Worksheet: Set ws1 = [COLOR="#FF0000"]Sheets("Sheet1")[/COLOR]

be

  ' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.
  ' This is better than using a sheet name in case the user changes the tab name at a later date.
     Dim ws1 As Worksheet: Set ws1 = [COLOR="#FF0000"]Sheet1[/COLOR]

although I don't think it makes much difference once it is assigned to a variable.

 

[TotallyConfused]

Hi TotallyConfused, if I am reading what you have posted correctly and you are intending using the sheets codename rather than it's tab name shouldn't

  ' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.
  ' This is better than using a sheet name in case the user changes the tab name at a later date.
     Dim ws1 As Worksheet: Set ws1 = [COLOR=#FF0000]Sheets("Sheet1")[/COLOR]

be

  ' You didn't specify what sheet is being used, so I assumed we are using Code Sheet 1.
  ' This is better than using a sheet name in case the user changes the tab name at a later date.
     Dim ws1 As Worksheet: Set ws1 = [COLOR=#FF0000]Sheet1[/COLOR]

although I don't think it makes much difference once it is assigned to a variable.

Hello Mark
You may be right, I don't know. The first time I was introduced to the idea of using the sheet code name rather than its tab name, was here on this site. I've written it this way a few times since then and never had any problems, though your suggestion would save a little typing. To be honest, I've always assumed that was the way to write it and have never thought anything more about it. When I put this routine together, I set up a dummy worksheet with several rows of data in column 'B', then ran my code. To make sure everything was being processed correctly, I then added a few more rows of data and ran this macro again. Looking at what others, including what you posted, I see mine is a little longer, but I tried to keep in mind that JPringle has more information to give us, so I tried to lay some of the basic groundwork now. For example, that is the reason I assigned the number of data rows to a variable, which can be accessed in any future code if needed, without having VBA make that calculation again. I don't know if this is good thinking or not. Most of you people are far more experienced than I am. I do want to THANK YOU for pointing out where I might have written it different because I'm always open to new and better ways of coding.

TotallyConfused

 

[MARK858]

Hi TotallyConfused, perhaps the link below will clear up for you the difference ways of referencing sheets

https://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

Btw, shorter code doesn't necessarily mean better code