CODEWITHSUNDEEP
excelvba
shaadi
shaadi

Reputation: 171

How to change values of range in excel vba

I want to change value of range (Source) and paste to to another range (destination) in excel vba. Please note that forward slashes may vary in source.

For instance I want to do like this but from vba excel: enter image description here

psedu code:

ThisWorkbook.Sheets("Sheet1").Range(A1,A4).Value = Replace source string before last forward with variable.

variable = "C:Destination\"

Basically I am amending values of Source and want to paste in destination.

This Code works but paste same values from source to destination

ThisWorkbook.Sheets("Sheet1").Range(A1,A4).Value = ThisWorkbook.Sheets("Sheet1").Range(B1,B4).Value

Please help in am new to VBA, Thanks in advance.

Upvotes: 0

Views: 16440

Answers (2)

Rui Monteiro
Rui Monteiro

Reputation: 329

Here a simple example for increase and decrease cell values by 1 in a range:

Option Explicit

Dim actualRange     As Range
Dim cellRange       As Range


Public Sub increaseNumber()

    Set actualRange = Selection

    For Each cellRange In actualRange.Cells
        cellRange.value = cellRange.value + 1
    Next

End Sub

Public Sub decreaseNumber()

    Set actualRange = Selection

    For Each cellRange In actualRange.Cells
        cellRange.value = cellRange.value - 1
    Next

End Sub

Upvotes: -1

Jesse
Jesse

Reputation: 2074

This could achieve what you are trying to do, unless I am misunderstanding the question.

Could improve further by doing a count on range of populated cells in first column and setting it as the loop max iteration.

Dim Str As String
Dim Source As String
Dim Destination As String
Dim populated As Integer

Source = "C:\Source\"
Destination = "D:\Destination\"

N = Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To N
    Str = Cells(i, 1).Value
    Cells(i, 2).Value = Replace(Str, Source, Destination)
Next i

Upvotes: 2

Related Questions