Solved

Copying/Moving data on a worksheet

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now