Code Review?

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
140
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 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:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
140
& most importantly , apologies because its not much of a '''code''' to review. I feel me asking is like a kid asking a teacher / shop keeper for free candy. which is wrong. And at same time I feel its insulting to ask especially for such a basic piece of 'code'. I do feel that, so apologies, but I do want to be a better programmer and more versitile/able in my work if-when the need arises.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
What is in the cells of Column A... formulas or constants?
 

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
140
They are mostly formula results, but a mix of constants in there too for when a manual entering was necessary.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,142
Messages
5,599,971
Members
414,353
Latest member
ljhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top