There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop malware memory modem monitor motherboard netgear network printer problem ram registry repair router slow software sound toshiba trojan usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless xbox
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: Excel Macro -> Use LEFT Function with Paste Special

Reply  
Thread Tools
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
31-Oct-2009, 08:22 AM #16
Zack, some example code would be great
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
31-Oct-2009, 03:07 PM #17
No problem Tony, here are two examples...
Code:
Option Explicit

Dim wb As Workbook, ws As Worksheet
Dim rTest As Range, rDest As Range
Dim i As Long, arrData() As Variant, rColTemp As Range

Const iValueLen As Long = 4

Sub Test_LEFT_Array()
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    Set rTest = ws.Range("A1:A15")
    Set rDest = wb.Sheets("Sheet2").Range("A1")
    arrData = rTest.Value
    For i = LBound(arrData) To UBound(arrData)
        If Len(arrData(i, 1)) >= iValueLen Then
            arrData(i, 1) = Left(arrData(i, 1), iValueLen)
        End If
    Next i
    rDest.Resize(rTest.Rows.Count, rTest.Columns.Count).Value = arrData()
End Sub

Sub Test_LEFT_Formula()
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    Set rTest = ws.Range("A1:A15")
    Set rDest = wb.Sheets("Sheet2").Range("A1")
    rTest.Offset(0, 1).EntireColumn.Insert
    Set rColTemp = rTest(1, 1).Offset(0, 1).Resize(rTest.Rows.Count, rTest.Columns.Count)
    rColTemp.Formula = "=LEFT(RC[-1]," & iValueLen & ")"
    rDest.Resize(rTest.Rows.Count, rTest.Columns.Count).Value = rColTemp.Value
    rColTemp.EntireColumn.Delete
End Sub
Only the one has a loop, but I much prefer looping via code values in an array as opposed to interrogating cells in a loop, which makes Excel have to load all of the data and properties along with it everytime, and I'd rather do it once or twice at most. Not to say either of these are better, just my preferred method(s).

HTH
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
31-Oct-2009, 03:33 PM #18
Zack, Thanks, I almost wish I hadn't asked now.
I will have to take a good look at it to understand what you are doing.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
31-Oct-2009, 04:01 PM #19
LOL! Well I'll answer what I can. Guess I could've commented up the code. I'm sure someone could show better too, and there's more than one way to skin the cat.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
01-Nov-2009, 07:18 AM #20
Zack, I understood the Array and loop it was the second one that I needed to go through to understand, I wouldn't have thought of inserting a Column to do the conversion in.
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 12:34 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.