CODEWITHSUNDEEP
vbaexcel
Hugh_Kelley
Hugh_Kelley

Reputation: 1040

Reading Manipulating and Writing Data in VBA

If I have a column of values, how do I read the values into a variable in VBA, perform some operation on it and then write it to the next column? Seems mind numbingly simple but I haven't been able to find a simple guide.

for example:

Column A = 1, 4, 5, 7

without writing formulas into column B

Dim A, B

A = column a

B = log(A)

write the values of B to the next column.

Thanks!

Upvotes: 2

Views: 2232

Answers (3)

brettdj
brettdj

Reputation: 55692

Rather than looping through cell by cell you would write directly to the worksheet using a LOG formula (no need for an array as the manipulation can be used directly with inserting a formula):

Sub LikeThis()
Dim rng1 As Range
`work on A1:A20 and write to B1:B20
Set rng1 = [a1:a20]
rng1.Offset(0, 1).FormulaR1C1 = "=IF(RC[-1]>0,LOG(RC[-1]),""not valid"")"
End Sub

Upvotes: 0

MatthewD
MatthewD

Reputation: 6791

If you want to loop through a whole sheet you can do it like this.

Dim lRow as long
Dim strA as string
Dim strB as string

Dim ws As Excel.Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")

lRow = 1
Do While lRow <= ws.UsedRange.Rows.count

   'Read the data from columnA
   strA = ws.Range("A" & lRow).Value

   'do something with the value you got from A
   strB  = strA & "some other text"
   strB = log(strA)

   'Write it to C
   ws.Range("C" & lRow).Value = strB

   lRow = lRow + 1
   ws.Range("A" & lRow).Activate
Loop

Or if you just want a certain predefined row it would be more hard coded like this.

'Read the data from columnA
strA = ws.Range("A6").Value

'do something with the value you got from A
strB  = strA & "some other text"
strB = log(strA)

'Write it to C
ws.Range("C6").Value = strB

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

Try this macro:

Sub test()
    Dim cel As Range, rng As Range
    Dim logCel As Double
    Dim lastRow As Integer

lastRow = Cells(1048576, 1).End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(lastRow, 1)) 'Create a range to search

For Each cel In rng
    If Not IsEmpty(cel) Then 'If the cell has a value in it
        logCel = Log(cel) 'add the LOG of the value to a variable
        cel.Offset(0, 1).Value = logCel 'In the cell to the right of the cell, put the log
    End If
Next cel

End Sub

To learn about setting cell values and such, I highly recommend using the macro recorder, then looking through the macro when you're done. Start the recorder, then enter a value into a cell, then in the one next, enter a log of that one, and you'll get some idea of how VBA works.

Upvotes: 0

Related Questions