Simple IF-THEN formatting

[JasonBarnes]

What's the most simple way to transfer values to other rows based on the value in one column. A sample of the data would be as follows. Basically I would need to transfer the name/city/state to the other rows based on the 3 digit number.

123
123 john raleigh nc
345 mark new york ny
566
456
345

[Steffen Thomsen]

Hi,

It's a bit vague what you need to accomplish.

Can you post a sample workbook.

A sheet with the raw data
A sheet with the end result

Then it's easier to get the code right from the start.

Steffen Thomsen

[JasonBarnes]

Where the yellow is highlighted I would like to fill the information from the corresponding and matching ID number.

[JasonBarnes]

Sorry I left out the file.

[Steffen Thomsen]

Hi JasonBarnes,

The values you need inserted do they come from a file or should they be harcoded into the code?

Steffen Thomsen

[Steffen Thomsen]

Hi again,

Example of a hardcoded sollution

Please Login or Register  to view this content.

[watersev]

hi, JasonBarnes, please check attachment, run code "test"

[protonLeah]

@JasonBarnes:
1. Originally, you gave a sample of the "data" which implies that it is the source which is to be operated on.
2. Your data consists of six rows; however, you didn't say if sample is all in one cell or distributed across four columns. For instance, given:
123 john raleigh nc
is that all in column A or is it spread across A - D?

3. You said: ... transfer the name/city/state to the other rows based on the 3 digit number ...

Did you really mean "rows" or should that be columns? You did not explain how the ID would determine which row the data should be transfered to (based on the 3 digit number...)

4. You posted a sample workbook; but, it looks like the result you are looking for without giving a sample of the source to be operated on. (again, there is the row/column confusion).

@waterserv:
1. Where did the data you used come from?
2. Your code is very cryptic and unreadable due to the x, y z, i, etc. names you use for variables your use of [a1] vs. Range("A1").Value, etc., most unfortunate!
3. Given the cryptic nature of your coding, I would expect copious commenting to help others to follow your logic: There are no comments;

4. Others who interested in contributing are probably disinclined after seeing your work.
What did you do and why?

[watersev]

@protonLeah

0. My nick is watersev
1. Data comes from data range on the sheet.
2. I assumed that counters for loops are pretty clear to understand. The code updated with comments and more obvious names though to me the code is much harder to read this way.
[a1] is the same as range("a1"). Yes, I know that it is slower to use [a1] but it's shorter to write and if it is used less then 15 times per code its use is unnoticable in terms of code execution time.
3. I do not post comments on codes until I asked for due many TS has a one-time need for the code and are not interested how it works as far as it gives the result they need.

[abousetta]

Hi protonLeah and watersev,

If I may give my five cents on the subject, I think you are both correct. Watersev, you are writing code as it comes naturally to you (as short as possible, using arrays as much as possible, etc) and there is nothing wrong with that. At the same time, I understand and second protonLeah's concern that even though the solution may (or may not) resolve the OP's question at hand, the code is almost impossible to decipher by a non-vba expert. Most people, myself first and foresmost, who search for answers on forums and through search engines are still learning and trying to use bits and pieces of available code to manage their problems before seeking help. If a beginner or possible intermediate programmer comes across your code, they might just skip it because they can't grapple with the concepts or shortcuts you are using in your code. Personally, I like John Walkenbach's suggestions to use variable names that have meaning and to add comments as much as possible to your code. If for no other purpose, it turns your 'one-time' code into a learning module for others who view your code.

I hope my comments will not discourage you in any way, but on the contrary, stimulate you to post more reader-friendly code. Even us beginners like to see advanced programmign skills, as long as we can at least understand what the programmer is attempting to accomplish.

Hope this helps.

abousetta