Solved

Copying/Moving data on a worksheet

Posted on 2014-12-23
5
97 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

623 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