Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copying/Moving data on a worksheet

Posted on 2014-12-23
5
Medium Priority
?
99 Views
Last Modified: 2014-12-23
Hi,

I need a VBA solution to look at Column L and if any row contains the word "NULL", copy the word "NULL" to Column "R" then copy whatever is in Column K to Column L.

Thanks in advance,

swjtx99Example.xlsx
0
Comment
Question by:swjtx99
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40515564
Create a Macro and add the code below:

  Dim Myrow As Long
  Myrow = 1
  While Range("L" & Myrow).Value <> ""
    If UCase(Range("L" & Myrow).Value) = "NULL" Then Range("R" & Myrow).Value = "Null"
    Range("L" & Myrow).Value = Range("K" & Myrow).Value
    Myrow = Myrow + 1
  Wend


Regards
0
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40515568
Sorry... I forgot to close the "if". Please use the code below:

  Dim Myrow As Long
  Myrow = 1
  While Range("L" & Myrow).Value <> ""
    If UCase(Range("L" & Myrow).Value) = "NULL" Then
        Range("R" & Myrow).Value = "Null"
        Range("L" & Myrow).Value = Range("K" & Myrow).Value
    End If
    Myrow = Myrow + 1
  Wend
0
 

Author Comment

by:swjtx99
ID: 40515668
Hi fabriciofonseca,

Sorry, I just found I have some blanks (empty cells in column L) and the code stops at the first blank. Can this be revised to go from the bottom up or skip any blanks in column L?

Thanks,

swjtx99
0
 
LVL 8

Accepted Solution

by:
fabriciofonseca earned 2000 total points
ID: 40515693
Dim Myrow As Long
  For Myrow = 1 To 65536 'you can change the 65536 and decrease / increase for any row quantities you want / need
    If UCase(Range("L" & Myrow).Value) = "NULL" Then
        Range("R" & Myrow).Value = "Null"
        Range("L" & Myrow).Value = Range("K" & Myrow).Value
    End If
  Next Myrow
0
 

Author Closing Comment

by:swjtx99
ID: 40515729
Thanks fabriciofonseca,

Works great!

Regards,

swjtx99
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question