Loop through rows making each line 80 Characters long

[Dave01]

Hi,


Ive come to a part of my code Im stuck on. I can get the code to work great with only one cell value, but I need it to do every row of data.
Each line would need to be 80 characters long

Sub test()

Dim name1 As String * 80
Dim var1, var2 As String

Range("D2").Select
var1 = ActiveCell.Value
name1 = Range("D2")
var2 = name1
Range("D2").Select
ActiveCell.Value = var2

End Sub

I guess it has to loop down until it finds a blank row then next ? (continues program)

or stores D2 as a value, does it thing, next D3 enter value, do it again etc, I just dont know how to make it work on multiple lines,

tried to use LastRow = Cells(Rows.Count, "A").End(xlUp).Row (normally data in row A) but this seems to only work for counting and formulas, not storing values ( which it wount because its a count)


any ideas please ?


thanks

David


Data to go in D2

10-00000002009999813742844Mrblobby
10-0000001881232253224813742844Thekabaabman
10-00000002009999813742844Mrblobby
10-0000001881232253224813742844Thekabaabman
10-00000002009999813742844Mrblobby
10-0000001881232253224813742844Thekabaabman

 

[bferraz]

Hey, you can adapt your code to this to have a loop on rows:

Sub test()
    Dim name1 As String * 80
    Dim var1, var2 As String
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row 'Find the last non-empty row in column D
    For i = 2 To lastRow 'Start the loop at row 2 and go until the last non-empty row
        var1 = Cells(i, "D").Value 'Get the value of the current cell
        name1 = Cells(i, "D").Value 'Store the value in the name1 variable
        var2 = name1 'Copy the value to the var2 variable
        Cells(i, "D").Value = var2 'Put the value back into the cell
    Next i 'Move to the next row
End Sub

or this

Sub test()
    Dim name1 As String * 80
    Dim var1, var2 As String
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row 'Find the last non-empty row in column D
    For Each cell In Range("D2:D" & lastRow) 'Start the loop at row 2 and go until the last non-empty row
        var1 = cell.Value 'Get the value of the current cell
        name1 = cell.Value 'Store the value in the name1 variable
        var2 = name1 'Copy the value to the var2 variable
        cell.Value = var2 'Put the value back into the cell
    Next 
End Sub

but if you want to make more changes to your code, I think this code might work better using LEFT:

Sub test()
    Dim rangeToProcess As Range
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row 'Find the last non-empty row in column D
    Set rangeToProcess = Range("D2:D" & lastRow) 'Define the range to process
    For Each cell In rangeToProcess
        cell.Value = Left(cell.Value, 80) 'Truncate the text of the cell to 80 characters
    Next
End Sub

 

[Dave01]

2nd one worked perfect, the third didnt do anything to the data, I ran it a few times nut didnt affect the length at all.

Im going with the second one, which fits into my main program nicely, and going forward I can use this code in other programs too.

Many thanks for your help,

David

 

[Dave01]

Hi, just ran into another issue, same problem but with different results,

This is the number
990000000000000229631000007

if I use the same code ( the second one on this number) I get 9.9E+28 and only gives me characters of 7

if I use this code I get 990000000000000229631000007 0

=Len(select the number) gives 81 characters, change string to 79 gives me 80, but this annoying 0 keeps appearing at the end,


Dim myVal As String * 80
Dim myNum As Integer

myVal = ActiveCell.Value
myVal = myVal & myNum

ActiveCell.Value = myVal & myNum

any ideas ?

thanks

David

​

 

[Dave01]

ok fixed it with this

1) turn integer to string

but you get a 0 at the end,

2) left(number,80) - removes 0

must be a better way of doing it then twice, but it works



Dim myVal As String * 80
Dim myNum As Integer


Range("A" & lastRow + 2 & ":A" & lastRow + 2).Select

myVal = ActiveCell.Value
myVal = myVal & myNum
ActiveCell.Value = myVal & myNum

Range("D" & lastRow + 2 & ":D" & lastRow + 2).Select
ActiveCell.FormulaR1C1 = "=Left(RC[-3],80)"

Selection.Copy
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False