Solved

Copying/Moving data on a worksheet

Posted on 2014-12-23
5
95 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

756 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