First of all apologies . I could not find a code review forum or service here (even if it does exist here). so sorry if im out of place. 2nd of all , deep apologies again; very few or hardly anyone at work will assist and whle at work I don't get the time opportunity to learn better code - too busy doing the actual job. 3rdly , sorry if this is a too basic of a question from someone (me) you don't owe anything to. But if you don't ask you don't get they say (it is said). I was seeking for pointers/tips/hints on Alternative better methods to do this, which I feel there are many.
The macro iterates over a range, row by row, checks if the values are one thing or another, and changes the offset cell next to it to a specific value I am setting . I feel this is very primative/stone age and easy. Im seeking better , more dynamic, efficient , extensive-applicable to other situations way to do this. (I know for instance i could plug the values to search for in an array (or in sheet2 colA - , reading those values into an array, and creating a kind of lookup relationship between sheet1 A and sheet2 A - between the terms) . I also know I could also use range evaluate method too to evaluate over the range (as opposed to cell by cell) ; how would that effect the check?
But I was specifically also asking if there is way to do this without any
) no? *), so how to implimement counif or lookup logic without cell B's change? and NO If statement or If-logic.
*what I used to do in that case, by formula, is do the vlookup on column C or Offset far off in column Z, and filter whole table, and then use =to the results of the vlookup, or embed the vlookup with more iF's in columB from the begining. Or just copy paste make a new sheet .
I am really seeking more better code or ways to do this. There are many. I am happy withe way Im finding the row range to process the loop, and happy with my error-case handler, but even that could be optimized no??
Any advice/pointers or help how to make the above more efficient, dynamic, better would be appreciated. If this is not the place to ask - sincere apologies. Ill have to buy a book or do myself. Apologies. Take care & thanks for helping me.
The macro iterates over a range, row by row, checks if the values are one thing or another, and changes the offset cell next to it to a specific value I am setting . I feel this is very primative/stone age and easy. Im seeking better , more dynamic, efficient , extensive-applicable to other situations way to do this. (I know for instance i could plug the values to search for in an array (or in sheet2 colA - , reading those values into an array, and creating a kind of lookup relationship between sheet1 A and sheet2 A - between the terms) . I also know I could also use range evaluate method too to evaluate over the range (as opposed to cell by cell) ; how would that effect the check?
But I was specifically also asking if there is way to do this without any
IF statements. (yeah, a vlookup, but a vlookup would overwrite column B (unless I had the vlookup with all the neccessisary if conditions in Coumb B in the first place!! ) no? *), so how to implimement counif or lookup logic without cell B's change? and NO If statement or If-logic.
VBA Code:
Sub MacroSubIterateOverRangeOffsetResults()
Dim myCell As Range
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each myCell In Range("A1:A" & LastRow)
If IsError(myCell.Value) Then
myCell.Offset(0, 1) = "Do you know you had an error in " & myCell.Address & "???"
Else:
If myCell.Value = "Italy" Or myCell.Value = "Spain" Then
myCell.Offset(0, 1) = 0
End If
End If
Next myCell
End Sub*what I used to do in that case, by formula, is do the vlookup on column C or Offset far off in column Z, and filter whole table, and then use =to the results of the vlookup, or embed the vlookup with more iF's in columB from the begining. Or just copy paste make a new sheet .
I am really seeking more better code or ways to do this. There are many. I am happy withe way Im finding the row range to process the loop, and happy with my error-case handler, but even that could be optimized no??
Any advice/pointers or help how to make the above more efficient, dynamic, better would be appreciated. If this is not the place to ask - sincere apologies. Ill have to buy a book or do myself. Apologies. Take care & thanks for helping me.
Last edited:
