UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Macro To Auto-fill Excel Table - Loop Through Columns?, Office 2013    
 
   
freespiritcheris...
post Apr 30 2019, 09:26 AM
Post#1



Posts: 125
Joined: 19-October 04



I need to create a Macro that will automate the following.

Cell A1 = 0 Then

Check next cell B1 IsNull.

If True, Insert Value 78.

If False, Goto next row,
change value one cell back, at a time, to value 71, till it arrives back

to column A.

To do this till last Column 'BS1'.

It needs to loop through 50 Rows. Once done, the data is permanent.

Attached File  autodimacroBefore.JPG ( 61.83K )Number of downloads: 10


Desired result is as Pic below.

EXAMPLE OF PROCESS (as in pic below)

Check Cell B1 (..it equals 1 in this eg)
Goto next row
backward One Cell (A1) Insert value 71.
Check Cell C1 (it is null), Insert Value 78.
Check Cell D1 (it is null), Insert Value 78.
Check Cell E1 (it is null), Insert Value 78.
Check Cell F1 (it is null), Insert Value 78.
Check Cell G1 (is NOTNULL, it equals 6)
Goto next row
backward One Cell (F2) Insert value 71.
Goto next row
backward One Cell (E3) Insert value 71.
Goto next row
backward One Cell (D4) (is NOTNULL, it equals 3)
Goto next row
backward One Cell (C5) Insert value 72.
Goto next row
backward One Cell (B6) Insert value 72.
Goto next row
backward One Cell (A7) Insert value 72. (End)

Check Cell H1 (it is null), Insert Value 78.
Goto next row
backward One Cell (G2) Insert value 78.
Goto next row
backward One Cell (F3) Insert value 78. and so on


Note: 71, 72, 73, 74, 75, 76, 77 (Red) represents a count. 71 = 1, 72 = 3, 73 = 3 to indicate the count of 'Black' digits in diagonal from top row to last left col.

There is no header row.
This post has been edited by freespiritcherishes: Apr 30 2019, 09:38 AM
Attached File(s)
Attached File  autodimacroaft.jpg ( 115.08K )Number of downloads: 10
 
Go to the top of the page
 
freespiritcheris...
post Apr 30 2019, 09:34 AM
Post#2



Posts: 125
Joined: 19-October 04



The loop would cycle through 50 records but the macro will be applied to process up to 8,000 records in other tables. coffee1.gif

Thank you in advance for any help.


This post has been edited by freespiritcherishes: Apr 30 2019, 09:40 AM
Go to the top of the page
 
ADezii
post May 1 2019, 06:00 AM
Post#3



Posts: 2,373
Joined: 4-February 07
From: USA, Florida, Delray Beach


Just had a chance to look at this Thread. I'll try to wrap my head around a solution and can back to you when, and if, I come up with something.
Go to the top of the page
 
ADezii
post May 1 2019, 09:57 AM
Post#4



Posts: 2,373
Joined: 4-February 07
From: USA, Florida, Delray Beach


I had a brief look at your Project and came up with a couple of ideas but unfortunately I will not be able to get back to it for a few days. I created a Demo and restricted the Data Range to approximately what you had shown in your Post, namely A1:U21. I created Nested Loops which analyze each Cell within the specified Range, if the Cell is NULL the Value of 78 was inserted into that Cell. If the Cell contained a Value and if the Cell offset by +1 Row and -1 Column was NULL (Cell(row, col).Offset(1, -1)) I inserted a Random Value between 71 and 77 into it. The reason for this is that I am very confused as to which Value (71-77) is inserted and where. There is a User Defined Section that will eventually define the number of Rows, number of Columns, and Sheet Name. I'll stop rambling now and Upload the uncompleted Demo that I created. Hopefully, this will help and point you in the right direction.
This post has been edited by ADezii: May 1 2019, 09:58 AM
Attached File(s)
Attached File  Free_Spirit.zip ( 31.29K )Number of downloads: 6
 
Go to the top of the page
 
freespiritcheris...
post May 10 2019, 06:10 AM
Post#5



Posts: 125
Joined: 19-October 04



Hi Adezli,

Thank you so much for that fine example as a first step and very cleverly done I must say. I have attached a sample spreadsheet to give more clarity of the desired result. The table structure with its columns will remain always the same, but the data changes daily. It is an arduous task so a macro is very much needed and I imagine to be quite complex. I have no idea where to begin. I looked at your amazing example and have no idea how you achieved that either. notworthy.gif

Thank you in advance.


Attached File(s)
Attached File  Task_required.zip ( 34.58K )Number of downloads: 2
 
Go to the top of the page
 
ADezii
post May 10 2019, 07:32 AM
Post#6



Posts: 2,373
Joined: 4-February 07
From: USA, Florida, Delray Beach


This may take some time, if I can come up with a resolution at all. I'll take another look in my spare time, be patient. One question to start off with: If the move from a Black Cell, either 1 Row Down/1 Col Left or 1 Row Down/1 Col Right would result in another Black Cell, then the assignment of Rd & Counts are cancelled (ignored)?
This post has been edited by ADezii: May 10 2019, 07:55 AM
Go to the top of the page
 
freespiritcheris...
post May 10 2019, 03:19 PM
Post#7



Posts: 125
Joined: 19-October 04



Hi Adezil,

Oh do please take your time, I appreciate your assistance. If a black number is present, YES, it is ignored, in that instance. But when the macro comes to that black number that was ignored, it would need to be counted and the count inserted 1 row down, 1 col left. In this pic 2 'black numbers are ignored and the count inserted after the 3rd count.. I hope this makes sense..

any help or code to study to achieve this would be useful.

Thanks again Adz thanks.gif


Attached File(s)
Attached File  DIAG.png ( 35.73K )Number of downloads: 4
 
Go to the top of the page
 
ADezii
post May 11 2019, 09:10 AM
Post#8



Posts: 2,373
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I am still having a problem wrapping my head around this one, so I am taking an entirely different approach. I am sending an Attachment that consists of a single Worksheet with only a subset of the Data in the Master Sheet. The Attachment conforms to the Master in some regards but is only meant for testing purposes. There are 23 'Black' Cells within a confined Range of N1:AF14. When you have time, simply fill in the appropriate Cells with whatever Background and Count that needs to be associated with them and send the Attachment back to me. Possible Values may be (example below):
    CODE
    B71 - Blue Background w/ Count = 71
    B73 - Blue Background w/ Count = 73
    R81 - Red Background w/ Count = 81
    R82 - Red Background w/ Count = 82
    G91 - Green Background w/ Count = 91
  2. In this manner maybe I can make the Logic a little more clearer.

This post has been edited by ADezii: May 11 2019, 09:12 AM
Attached File(s)
Attached File  Cells.JPG ( 11.06K )Number of downloads: 0
Attached File  Info_Needed.zip ( 10.53K )Number of downloads: 1
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th June 2019 - 05:50 PM