jish
01-18-2024, 11:44 AM
Hi, Could someone please help “translate” into English what this bit of code below is doing (if that is easy to do)? The person who wrote it has left and I (who know next to nothing about coding) am trying to tweak the VBA code because the macro is no longer working correctly. I think the problem is that we combined years of a publication, so instead of labeling an embedded object in the worksheet as “…in 2019” we are trying to have it say “…in 2020-21,” for example. Currently it is returning things like "aho in 2 2020 and 2021 text" and I just can’t see where it is assigning the year. (We would like it to label the embedded icon as "Idaho in 2020-21 text." The code extracts that information for the place and year, I believe, from a title that appears on the first tab of the Excel workbook. Very magical, but also confusing. Have hundreds of embedded objects to name individually, so... Thanks for any help you can give!
' For every character in input string, copy digits to '
' return string. '
For l = 1 To Len(S)
If Mid(S, l, 1) >= "0" And Mid(S, l, 1) <= "9" Then
retval = retval + Mid(S, l, 1)
End If
Next
If Len(retval) = 8 Then
year = Left(retval, 4) & " and " & Right(retval, 4)
Else
If Len(retval) = 4 Then
year = retval & ""
Else
year = "Unsure"
End If
End If
'get the name for the embedded file
If InStr(1, ActiveCell.FormulaR1C1, "Cereal Industr") = 0 Then
newItem = Left(ActiveCell.FormulaR1C1, Len(ActiveCell.FormulaR1C1) - (4 + Len(year)))
newItem = newItem & " " & year & " Text"
Else
StrLen = Len(ActiveCell.FormulaR1C1)
str1 = Right(ActiveCell.FormulaR1C1, StrLen - (20 + Len(year)))
newItem = Left(str1, StrLen - (28 + Len(year)))
newItem = newItem & " " & year & " Text"
End If
' For every character in input string, copy digits to '
' return string. '
For l = 1 To Len(S)
If Mid(S, l, 1) >= "0" And Mid(S, l, 1) <= "9" Then
retval = retval + Mid(S, l, 1)
End If
Next
If Len(retval) = 8 Then
year = Left(retval, 4) & " and " & Right(retval, 4)
Else
If Len(retval) = 4 Then
year = retval & ""
Else
year = "Unsure"
End If
End If
'get the name for the embedded file
If InStr(1, ActiveCell.FormulaR1C1, "Cereal Industr") = 0 Then
newItem = Left(ActiveCell.FormulaR1C1, Len(ActiveCell.FormulaR1C1) - (4 + Len(year)))
newItem = newItem & " " & year & " Text"
Else
StrLen = Len(ActiveCell.FormulaR1C1)
str1 = Right(ActiveCell.FormulaR1C1, StrLen - (20 + Len(year)))
newItem = Left(str1, StrLen - (28 + Len(year)))
newItem = newItem & " " & year & " Text"
End If