Copy/paste a value into the cell with formula

[ColPal]

Hi there,

is it possible to copy a value from one sheet and put it into another cell in another sheet of the same book. The problem is that the target cell alredy contains formula which I want to stay. Instead of overcopying the new value I want to merge them. Like this: Formula-"value from the another sheet"

 

[Joe4]

Welcome to the Board!

You would have to use VBA to do something like that.
If you want to do that, you would clearly need to lay out the conditions and details (i.e. sheets and ranges involved).

 

[ColPal]

 

[Joe4]

You did not provide the exact sheet names and exact range references, like I asked.
Without them, we can provide either a very generic answer or make our own assumptions, and then you are left to figure out how to adapt it to your situation.
How comfortable are you trying to do that on your own?
If not very, it would be best to provide the exact details so we can tailor our response to your exact scenario.

 

[Joe4]

Also, are the two lists always the same account numbers in the exact same order, where we just match up first row on Sheet1 with first row on Sheet2, or might they be in different orders and require the use of lookups?

 

[ColPal]

You did not provide the exact sheet names and exact range references, like I asked.
Without them, we can provide either a very generic answer or make our own assumptions, and then you are left to figure out how to adapt it to your situation.
How comfortable are you trying to do that on your own?
If not very, it would be best to provide the exact details so we can tailor our response to your exact scenario.

KK,

so the source sheet (2) has a name of a current Month. The target sheet (1) - always 3125333. In the sheet 1 (3125333) in the col A are located numbers of accounts. In the column B formula Vlookup. Sheet 2 (name of the current month) contains the numbers of accounts related to Sheet 1 and different values related for these accounts. The task is to find a proper account in the Sheet 1 (3125333), and put the values from Sheet 2 in the Col B but without overrighting the result as a value. The result must contains the previous formula and the relating ti this account value from Sheet 2. Ex B2 = Vlookup(...)-value from Sheet 2

 

[ColPal]

Also, are the two lists always the same account numbers in the exact same order, where we just match up first row on Sheet1 with first row on Sheet2, or might they be in different orders and require the use of lookups?

I need help with copying the values directly near the formula, not insted of it. Is it possible. i have already a macro for finding the proper account. But can't make correct pasting

 

[Joe4]

i have already a macro for finding the proper account.

Then please post that code so we can work off of that!

 

[ColPal]

Sub CopyDataAndSubtractFromExistingValues()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim i As Long
Dim accountName As String
Dim accountRow As Range
Dim monthName As String
Dim monthColumn As Integer
Dim valueToSubtract As Double
Dim currentCell As Range

Set sourceSheet = ThisWorkbook.ActiveSheet

monthName = sourceSheet.Range("D1").Value

Set targetSheet = ThisWorkbook.Sheets("3125333")

monthColumn = 0
For i = 18 To 32
If targetSheet.Cells(6, i).Value = monthName Then
monthColumn = i
Exit For
End If
Next i

If monthColumn = 0 Then
MsgBox "Nie znaleziono kolumny dla miesiąca: " & monthName, vbExclamation, "Błąd"
Exit Sub
End If

lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "F").End(xlUp).Row


For i = 4 To lastRow
accountName = sourceSheet.Cells(i, "E").Value
If InStr(1, accountName, "Total", vbTextCompare) = 0 Then
valueToSubtract = sourceSheet.Cells(i, "F").Value

Set accountRow = targetSheet.Columns("A:A").Find(What:=accountName, LookIn:=xlValues, LookAt:=xlWhole)

If Not accountRow Is Nothing Then
Set currentCell = targetSheet.Cells(accountRow.Row, monthColumn)
currentCell.Value = currentCell.Value - valueToSubtract
Else
MsgBox "Nie znaleziono konta: " & accountName, vbExclamation, "Błąd"
End If
End If
Next i
End Sub

 

[Joe4]

Is this the line where you are trying to update the formula?

currentCell.Value = currentCell.Value - valueToSubtract

If so, then you do NOT want to use Value, but rather Formula, i.e.

currentCell.Formula = currentCell.Formula & "-" & valueToSubtract