1

Morning guys,

I have recently been tasked with being the person to update and monitor any VBA issues my currently company has, as the previous employee who was doing such has no left and there are no immediate plans to hire a replacement. Unfortunately my excel and VBA skills are rudimentary put politely, and youtube has only been able to help so much.

There is a macro used in one of the spreadsheets which checks and overwrites certain month end figures. This part of the macro runs fine, and when completed for each client an X should be input to column M (Labelled done) to signify this is done. The column N (labelled skip) is already filled with an X for those that should be skipped due to individual client technicalities.

The macro however appears to be filling in column N with the value x for when a client check is done. Have any of you ever encountered a similar issue with values being incorrectly assigned to the adjacent column?

Sub Values()

Application.ScreenUpdating = False

Dim EndRow As Integer
Dim i As Integer
Dim ValueDate As Date
Dim Cash As Double
Dim Value As Double
Dim APXRef As String
Dim d As Integer
Dim Overwrite As Boolean



Overwrite = Worksheets("Summary").Range("Y2").Value ' from checkboxes
EndRow = Range("J2").End(xlDown).Row
ValueDate = Range("P6").Value

If MsgBox("You are uploading with the following date: " & ValueDate & ", do 
you want to continue?", vbYesNo) = vbNo Then Exit Sub

For i = 2 To EndRow
APXRef = Range("J" & i).Value
Value = Range("L" & i).Value

If Range("M" & i) = "" And Range("N" & i) = "" Then

Worksheets("Summary").Activate
r = Range("A:A").Find(APXRef).Row
Range("B" & r).Select
Call GoToClient
d = Range("A10").End(xlDown).Row

If Range("A" & d).Value < ValueDate Then

Range("A" & d + 1).Value = ValueDate
Range("B" & d + 1).Value = Value
Range("D" & d + 1).FormulaR1C1 = "=((RC[-2]/(R[-1]C[-2]+RC[-1]))-1)*100"
Range("E" & d + 1).FormulaR1C1 = "=((((R[-1]C)*(RC[-1]))/100)+R[-1]C)"
Range("H" & d + 1).Value = Range("H" & d).Value


'Save client
If Overwrite = True Then
Call SaveClient
End If

'Return to Flow Tab
Worksheets("Flows").Activate
Range("M" & i).Value = "x"


Else
'skip
Worksheets("Flows").Activate
Range("N" & i).Value = "x"
End If



End If

Application.StatusBar = TabRef & "    " & Round(((i - 1) / (EndRow - 1)) * 
100, 1) & "% Complete"

Next i
Application.StatusBar = "Value Update Complete"



End Sub
Daniel
  • 11
  • 1
  • Could you please edit your question to include the code for `GoToClient` and `SaveClient` just in case they are causing the problem? – Mark Fitzgerald Aug 22 '18 at 08:58
  • Also if possible, can you share the sample worksheet/data for this? BTW most probably its the check "If Range("A" & d).Value < ValueDate" that is failing. A quick way to diagnose is put a separate values for M and N.. i.e. say **'x'** for M and **'y'** for N.So that you can double confirm the flow. – subdeveloper Aug 22 '18 at 09:01
  • Without diving into your code to much there are a few things I would do differently. You could offcourse have your reasoning, but most likely you can avoid using `.select`. How?, see [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Furtermore I would either declare your different sheets in a variable or something different like index numbering of sheets, like so `Sheets(1).Cells(....`. You won't need to activate any sheet first either. Maybe something is lost in all of this making your value ending up in an unintended place. – JvdV Aug 22 '18 at 09:19

0 Answers0