To get data to variable and display them in a summary quasi

QuestionEdit

QUESTION: I would like to get the information about ActiveCellRow into variable after my macro processing (when I insert "X" to coll AG and col E or S are not blank). I want to display all "impacted" rows in summary quasi "msgbox" (no case by case as you can see below). Then clear content of the variable. In case of nonfulfillment conditions in code -> if AG10 ="X" and E10 <> "", then nothing.

simple case for better understanding: if AG10 ="X" and E10 <> "" (e.g. E10 = 100) if AG11 ="X" and E11 <> "" .... if AG12 ="X" and E12 <> "" .... .... .... to get these info about impacted cellrow to variable and display following msgbox:

" You have to check the following cellrow - E10; E11; E12... because the cellrow were marked as final delivered and there are nonzero sum"

I appreciate your help.. BR, Martin

Sub FinalDeliveryCapex(ActiveTarget As Range)

Dim ECapex As Range, SCapex, ColAG Dim DateCap As Date Dim ActiveCellRow As Integer

ActiveCellRow = ActiveTarget.Row

ECapex = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "E") ' Year 2010 SCapex = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "S") ' Year 2011 ColAG = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "AG") ' value "X" DateCap = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "AS") ' Payment Date

If UCase(Trim(ColAG)) = "X" And ActiveTarget.Column = 33 Then

   If Year(DateCap) < 2011 Then
     If ECapex <> "" Then
       MsgBox ("..................E" & ActiveCellRow & ", .......................")  ' I would like to get this info into variable      
     End If
   End If
   If Year(DateCap) > 2010 Then
     If SCapex <> "" Then
     MsgBox ("...................S" & ActiveCellRow & ",.......................")  ' I would like to get this info into variable            
   
     End If
   End If

End If

-> display all content of variable (I mean summary of the above mentioned "msgbox") -> clear content of variable and close

End Sub

ANSWER: Martin,

the way you routine is set up, you call it with a specific cell so at most it can return a message to columns E and/or S or not message for that particular row. Since your example shows a message with multiple rows, then I assume you want to pass in a string variable that is initialize to nothing in the calling routine and has any appropriate cell appended to the message string. I added the variable "s" to the argument list and showed how you might append the cells addresses to it. If you want a separate message for column E and a separate message for column S, then pass in two string variables, perhaps sE and sS. Make appropriate adjustments in the code if that is the case. Sub FinalDeliveryCapex(ActiveTarget As Range, s as String)

Dim ECapex As Range, SCapex, ColAG Dim DateCap As Date Dim ActiveCellRow As Integer

ActiveCellRow = ActiveTarget.Row

ECapex = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "E") ' Year 2010 SCapex = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "S") ' Year 2011 ColAG = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "AG") ' value "X" DateCap = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "AS") ' Payment Date

If UCase(Trim(ColAG)) = "X" And ActiveTarget.Column = 33 Then

  If Year(DateCap) < 2011 Then
    If ECapex <> "" Then
      s1 = "E" & ActiveCellRow   ' add any other info you want in the concatenation
      MsgBox ("..................E" & ActiveCellRow & ", .......................") 
     End If
  End If
  If Year(DateCap) > 2010 Then
    If SCapex <> "" Then
      s2 = "S" & ActiveCell.row  ' add any other info you want in the concatenation
    MsgBox ("...................S" & ActiveCellRow & ",.......................")     
    End If
  End If

End If

if len(trim(s1)) = 0 and len(trim(s2)) = 0 then

  ' do nothing

elseif len(trim(s1)) <> 0 and len(trim(s2)) = 0 then

   s = s & s1 & ","

elseif len(Trim(s1)) = 0 and len(trim(s2)) <> 0 then

   s = s & s2 & ","

else

   s = s & s1 & "," & s2 & ","

End if

' possibly if len(trim(s)) <> 0 then

 msgbox s

end if ' but I thought you wanted to display it for many rows, not as you process each row.

end sub

---------- FOLLOW-UP ----------

QUESTION: , I changed my final intention :o). Your macro works fine, but I would like to rewrite stored data in the string variable, because in case of repeated push of a button it display previous info. Can you help me, please? Martin

AnswerEdit

Martin,

since s comes in as a argument to your function, I have no idea where it is declared or how you are managing it. If s is the variable you want to reset each time the function is called (and that didn't appear to be your original intention), then you could do that in this routine.

ActiveCellRow = ActiveTarget.Row

ECapex = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "E") ' Year 2010

becomes

ActiveCellRow = ActiveTarget.Row s = "" '<= reset the string at the top of the module ECapex = ThisWorkbook.Worksheets("Budget").Cells(ActiveCellRow, "E") ' Year 2010

That is what I understand you to ask.