How to change values of range in excel vba

Question

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:

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.

Answer 1

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

Answer 2

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